<?php

 Zend_Loader::loadClass("BaseControllerAuth");
 class AdminController extends BaseControllerAuth
 {
 	public function initialize(){
		Zend_Loader::loadClass("UserTypes");
		Zend_Loader::loadClass("CodesUtility");
	
		
		$auth = BaseAuth::getInstance();
	
		$this->setLayout("admin");
		$this->assign("pageTitle","Trade " . $this->getConfig()->client->name);
		$userTypes = new UserTypes();
		$userTypesArray = $userTypes->getUserTypesArray("code", "id");
		$this->assign("viewType",$userTypesArray[CodesUtility::USER_TYPE_ADMIN]);
		//$this->assign("controller","importer");
		if($userTypesArray[CodesUtility::USER_TYPE_ADMIN] != $auth->user_type_id) {
			$this->deAuthenticate();
		}
 	}
	
	public function deAuthenticate(){
		$auth = BaseAuth::getInstance();
		$auth->deAuthenticate();
 	}
	
	public function indexAction() {
		
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("EmailMessage");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//msgs
		$query=new QueryCreator();
		$query->addSelect(" count(*) as cnt ");
		$query->addFrom(EmailMessage::_tablename." as e ");
		$query->addWhere(" e.read=0 and e.recipient_org_id=".$auth->org_id);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("mail_count",$result[0]['cnt']);
		$this->render("home.tpl");	
	}
	
	//catch inquiries
	public function inquiriesAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->assign("menuUtility" ,$menuUtility);
		
		$this->render("inquiries.tpl");
	}
	public function inqCatchSelectViewAction(){
		$view=$this->getParam("view");
		$option='';
		switch($view){
			case "collector":
				//fishers
				Zend_loader::loadClass("Fishers");
				$fishers=new Fishers();
				$fishers->loadWhere("1 = 1",array("last_name"));
				foreach($fishers->toArray() as $fisher){
					$option.='<option value="'.$fisher['fisher_id'].'">'.
						$fisher['last_name'].', '.$fisher['first_name'].'</option>';
				}
				$label="Collector:";
			break;
			case "specie":
				//organism
				Zend_loader::loadClass("Organisms");
				$Organisms=new Organisms();
				$Organisms->loadWhere("1 = 1",array("scientific_name"));
				foreach($Organisms->toArray() as $org){
					$option.='<option value="'.$org['organism_id'].'">'.
						$org['name'].'</option>';
				}
				$label="Species:";
			break;
			case "site":
				//fishing area
				Zend_loader::loadClass("FishingAreas");
				$fishingAreas=new FishingAreas();
				$fishingAreas->loadWhere("1 = 1",array("fishing_area_name"));
				foreach($fishingAreas->toArray() as $fas){
					$option.='<option value="'.$fas['fishing_area_id'].'">'.
						$fas['fishing_area_name'].'</option>';
				}
				$label="Site:";
			break;
			default:
				$label="";
			break;
		}
		echo $result='<input type="hidden" name="hview" value="'.$view.'">
						<select name="myview">
							'.$option.'
						</select>
						';
	}
	public function inqCatchShowListAction(){
		
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("FishingArea");
		Zend_loader::loadClass("Fisher");
		
		$query = new QueryCreator();
		/*$query->addSelect(" r.date_created,r.date_updated,r.supplier_fishing_area_id,r.supplier_org_id,r.org_organism_id,
						f.last_name,f.first_name,fa.fishing_area_name,o.name,oz.name as organism_name,
						r.doa_quantity,r.reject_quantity,r.received_quantity");*/
		$query->addSelect(" r.date_created,r.date_updated,r.supplier_fisher_id,r.supplier_org_id,r.org_organism_id,
						f.last_name,f.first_name,o.name,oz.name as organism_name,fa.fishing_area_name,
						r.doa_quantity,r.reject_quantity,r.received_quantity ");
		$query->addFrom( Receipt::_tablename ." as  r ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " as oz on r.org_organism_id=oz.organism_id ");
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " as o on r.supplier_org_id=o.org_id ");
		//$query->addJoin(" LEFT JOIN ". FishingArea::_tablename . " as fa on r.supplier_fishing_area_id=fa.fishing_area_id ");
		$query->addJoin(" LEFT JOIN ". Fisher::_tablename . " as f on r.supplier_fisher_id=f.fisher_id ");
		$query->addJoin(" LEFT JOIN ". FishingArea::_tablename . " as fa on f.fishing_area_id=fa.fishing_area_id ");	
			
		if($this->getParam("collector"))
			$query->addWhere(" r.supplier_fisher_id=".$this->getParam("collector"));
		if($this->getParam("site"))
			$query->addWhere(" f.fishing_area_id=".$this->getParam("site"));
		if($this->getParam("specie"))
			$query->addWhere(" r.org_organism_id=".$this->getParam("specie"));
		
		if($this->getParam("f_date") && $this->getParam("t_date")){
			$fdate=date("Y-m-d H:i:s",strtotime($this->getParam("f_date")));
			$tdate=date("Y-m-d H:i:s",strtotime($this->getParam("t_date")));
		}
		if($fdate && $tdate)
			$query->addWhere(" r.date_created<='".$tdate."' AND r.date_created>='".$fdate."'");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		Zend_Loader::loadClass("MenuUtility");
		
		$this->setLayout("admin");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("inquiries_table.tpl");
	}
	
	//ORS inquiries
	public function inquiriesORSAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("Organizations");
		
		$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" 1=1 ");
		$this->assign("orgtypes",$orgtypes->toArray());
		$this->render("inquiries_ors.tpl");
	}
	public function inqORSEntitiesAction(){
		$this->setNoLayout();
		
		$type=$this->getParam("entity_type");
		if($type){
			Zend_loader::loadClass("Organizations");
			$orgs=new Organizations();
			$orgs->loadWhere("org_type='".$type."'");
			
			foreach($orgs->toArray() as $org)
				$result.='<option value="'.$org['org_id'].'" >'.$org['trading_name'].'</option>';
		}
		
		echo '	<select name="entity" id="entity" onChange="doajaxAction(\'/admin/inqORSBTS/entity/\'+document.MainQuery.entity.value,0,\'bts\')">
					<option value=""> --- </option>
					'.$result.'
				</select>';
	}
	public function inqORSBTSAction(){
		$entity=$this->getParam("entity");
		
		Zend_loader::loadClass("Shipments");
		Zend_loader::loadClass("Tanks");
		Zend_loader::loadClass("OrganizationOrganisms");
		
		$ships=new Shipments();
		$ships->loadWhere(" org_id='".$entity."'");
		$ship=$ships->toArray();
		
		$tanks=new Tanks();
		$tanks->loadWhere(" org_id='".$entity."'");
		$tank=$tanks->toArray();
		
		$organisms=new OrganizationOrganisms();
		
		$this->assign("batches",$ship);
		$this->assign("tanks",$tank);
		$this->assign("species",$organisms->getOrganismByOrgId($entity));
		$this->setNoLayout();
		$this->render("ors_bts.tpl");
	}
	public function inqORSShowListAction(){
		Zend_loader::loadClass("Shipment");
		Zend_loader::loadClass("ShipmentDetail");
		Zend_loader::loadClass("Inventory");
		Zend_loader::loadClass("Tank");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("OrganismAttribute");
		Zend_loader::loadClass("Size");
		Zend_loader::loadClass("InventoryDAA");
		
		$query = new QueryCreator();
		$query->addSelect(" s.shipment_id,sd.ship_quantity,t.name as tank_name, o.moid, 
							o.name, si.size, o.mac_certified, id.quantity_daa, 
							id.date_mortality, id.reason, s.date_created ");
		$query->addFrom(Shipment::_tablename." as s ");
		$query->addJoin(" LEFT JOIN ".ShipmentDetail::_tablename." as sd on s.shipment_id=sd.shipment_id ");
		$query->addJoin(" LEFT JOIN ".Inventory::_tablename." as i on i.inventory_id=sd.source_tank_inventory_id ");
		$query->addJoin(" LEFT JOIN ".Tank::_tablename." as t on i.tank_id=t.tank_id  ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as o on i.org_organism_id=o.organism_id ");
		$query->addJoin(" LEFT JOIN ".OrganismAttribute::_tablename." as oa on i.organism_attribute_id=oa.organism_attribute_id ");
		$query->addJoin(" LEFT JOIN ".Size::_tablename." as si on si.size_code=oa.size_code ");
		$query->addJoin(" LEFT JOIN ".InventoryDAA::_tablename." as id on i.inventory_id=id.inventory_id ");
		
		if($this->getParam("batch"))
			$query->addWhere(" s.shipment_id=".$this->getParam("batch"));
		if($this->getParam("tank"))
			$query->addWhere(" i.tank_id=".$this->getParam("tank"));
		if($this->getParam("specie"))
			$query->addWhere(" i.org_organism_id=".$this->getParam("specie"));
		
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("inquiries_ors_table.tpl");
	}
	
	//to be removed
	public function inquiriesWaterAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("Organizations");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("Tanks");
		$Tanks=new Tanks();
		$Tanks->loadWhere("1 = 1");
		$this->assign("tanks",$Tanks->toArray());
		
		$this->render("inquiries_water.tpl");
	}
	
	//Customer Order inquiries
	public function inquiriesCOAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" 1=1 ");
		$this->assign("orgtypes",$orgtypes->toArray());
		
		$this->setNoLayout();
		$this->render("inquiries_custord.tpl");
	}
	public function inqCOCountriesAction(){
	
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Country");
		Zend_loader::loadClass("Address");
		$option = "";
		$type=$this->getParam("entity_type");
		if($type){
			$query=new QueryCreator();
			$query->addSelect(" distinct c.country_code,c.country ");
			$query->addFrom( Organization::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." as a on a.address_id=o.primary_address_id ");
			$query->addJoin(" LEFT JOIN ".Country::_tablename." as c on a.country_code=c.country_code ");
			$query->addWhere(" o.org_type='".$type."'");
			//echo $query->createSQL(); die();
			$db = DBCon::instance();
			$result = $db->executeQuery($query->createSQL());
			//print_r($result);die();
			foreach($result as $row){
				if($row['country_code'])
					$option.='<option value="'.$row['country_code'].'" >'.$row['country'].'</option>';
			}
		}
		
		echo '
			<select name="country" id="country" onChange="doajaxAction(\'/admin/inqCOProvince/country/\'+document.MainQuery.country.value,0,\'province\')">
				<option value=""> --- </option>
				'.$option.'
			</select>';
	}
	public function inqCOProvinceAction(){
		$country=$this->getParam("country");
		Zend_loader::loadClass("Address");
		$option = "";
		if($country){
			$query=new QueryCreator();
			$query->addSelect(" Distinct a.province ");
			$query->addFrom(Address::_tablename." as a ");
			$query->addWhere(" a.country_code='".$country."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['province'])
					$option.='<option value="'.$row['province'].'" >'.$row['province'].'</option>';
			}
		}
		
		echo '
					<select name="province" id="province" onChange="doajaxAction(\'/admin/inqCOEntity/country/\'+document.MainQuery.country.value+\'/province/\'+document.MainQuery.province.value,0,\'entity\')">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	public function inqCOEntityAction(){
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Organization");
		
		$country=$this->getParam("country");
		$province=$this->getParam("province");
		$option = "";
		if($country AND $province){
			
			$query=new QueryCreator();
			$query->addSelect(" DISTINCT o.org_id, o.trading_name ");
			$query->addFrom(Address::_tablename." AS a ");
			$query->addJoin(" LEFT JOIN ".Organization::_tablename." AS o ON o.primary_address_id = a.address_id ");
			$query->addWhere(" a.province = '".$province."'
								AND a.country_code='".$country."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['org_id'])
					$option.='<option value="'.$row['org_id'].'" >'.$row['trading_name'].'</option>';
			}
		}
		
		echo '
					<select name="entity" id="entity" onChange="doajaxAction(\'/admin/inqCOPO/entity/\'+document.MainQuery.entity.value,0,\'po\')">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	public function inqCOPOAction(){
		$entity=$this->getParam("entity");
		$option = "";
		if($entity){
			
			Zend_loader::loadClass("purchaseOrders");
			$pos=new purchaseOrders();
			$pos->loadWhere(" org_id=".$entity);
			$result=$pos->toArray();

			foreach($result as $row){
				if($row['po_id'])
					$option.='<option value="'.$row['po_id'].'" >'.$row['po_no'].'</option>';
			}
		}
		
		echo '
					<select name="po" id="po">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	public function inqCOShowListAction(){
	
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("purchaseOrder");
		Zend_loader::loadClass("purchaseOrderDetail");
		Zend_loader::loadClass("Shipment");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Size");
		
		$po=$this->getParam("po");
		$tdate=$this->getParam("tdate");
		$fdate=$this->getParam("fdate");
		
		$query=new QueryCreator();
		$query->addSelect(" o.po_id,org.trading_name,o.po_code,o.commission,o.date_order,od.order_quantity,s.total_quantity,specie.moid,specie.name, sizes.size,od.order_amount ");
		$query->addFrom( purchaseOrder::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as org on o.org_id=org.org_id ");
		$query->addJoin(" LEFT JOIN ".purchaseOrderDetail::_tablename." as od on od.po_id=o.po_id ");
		$query->addJoin(" LEFT JOIN ".Shipment::_tablename." as s on s.po_id=o.po_id ");
		$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.org_organism_id=od.org_organism_id ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as specie on specie.organism_id=oo.organism_id ");
		$query->addJoin(" LEFT JOIN ".Size::_tablename." as sizes on sizes.size_code=od.size_code ");
		
		if($po)
			$query->addWhere(" o.po_id='".$po."' ");
		if($tdate)
			$query->addWhere(" o.date_order<='".$tdate."' ");
		if($fdate)
			$query->addWhere(" o.date_order>='".$fdate."' ");
			
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("inquiries_co_table.tpl");
	}
	
	//Organism Dispatch   //reusing customer order
	public function inquiriesODAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" 1=1 ");
		$this->assign("orgtypes",$orgtypes->toArray());
		
		$this->setNoLayout();
		$this->render("inquiries_orgdispatch.tpl");
	}
	public function inqODShowListAction(){
		Zend_loader::loadClass("Shipment");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("purchaseOrder");
		Zend_loader::loadClass("Shipmethod");
		Zend_loader::loadClass("purchaseOrderDetail");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Size");
		
		$po=$this->getParam("po");
		$tdate=$this->getParam("tdate");
		$fdate=$this->getParam("fdate");
		
		$query=new QueryCreator();
		$query->addSelect(" s.shipment_id,o.trading_name,orders.po_code,s.date_shipment,s.time_shipment,
						orders.date_order, sm.name as transpo,organism.moid,organism.name,
						od.order_quantity,s.total_quantity,size.size ");
		$query->addFrom(Shipment::_tablename." as s ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o on s.customer_org_id=o.org_id ");
		$query->addJoin(" LEFT JOIN ".purchaseOrder::_tablename." as orders on s.po_id=orders.po_id ");
		$query->addJoin(" LEFT JOIN ".Shipmethod::_tablename." as sm on sm.id=s.ship_method_id ");
		$query->addJoin(" LEFT JOIN ".purchaseOrderDetail::_tablename." as od on od.po_id=s.po_id");
		$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.org_organism_id=od.org_organism_id ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as organism on organism.organism_id=oo.organism_id ");
		$query->addJoin(" LEFT JOIN ".Size::_tablename." as size on size.size_code=od.size_code ");
		if($po)
			$query->addWhere(" s.po_id='".$po."' ");
		if($tdate)
			$query->addWhere(" s.date_shipment<='".$tdate."' ");
		if($fdate)
			$query->addWhere(" s.date_shipment>='".$fdate."' ");
			
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("inquiries_od_table.tpl");
	}
	
	//INQ TAC
	public function inquiriesTIAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" org_type='Exporter' OR org_type='Importer' ");
		$this->assign("orgtypes",$orgtypes->toArray());
		
		$this->setNoLayout();
		$this->render("inquiries_tac.tpl");
	}
	public function inqTCountriesAction(){
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("OrganizationAddress");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Country");
		
		$type=$this->getParam("entity_type");
		if($type){
			$query=new QueryCreator();
			$query->addSelect(" distinct a.country_code,c.country ");
			$query->addFrom(Organization::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".OrganizationAddress::_tablename." as oa on oa.org_id=o.org_id ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." as a on a.address_id=oa.address_id ");
			$query->addJoin(" LEFT JOIN ".Country::_tablename." as c on c.country_code=a.country_code ");
			$query->addWhere(" o.org_type='".$type."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['country_code'])
					$option.='<option value="'.$row['country_code'].'" >'.$row['country'].'</option>';
			}
		}
		
		echo '	<select name="country" id="country" onChange="doajaxAction(\'/admin/inqTFMA/country/\'+document.MainQuery.country.value,0,\'fma\')">
						<option value=""> --- </option>
						'.$option.'
				</select>';
	}
	public function inqTFMAAction(){
		$country=$this->getParam("country");
		if($country){
			Zend_loader::loadClass("FishingAreas");
			$fas=new FishingAreas();
			$fas->loadWhere(" country_code='".$country."' ");
			
			foreach($fas->toArray() as $row){
				if($row['org_id'])
					$option.='<option value="'.$row['fishing_area_id'].'" >'.$row['fishing_area_name'].'</option>';
			}
		}
		
		echo '<select name="fma" id="fma">
					<option value=""> --- </option>
					'.$option.'
				</select>';
	}
	public function inqTShowListAction(){
	
		Zend_loader::loadClass("Tac");
		Zend_loader::loadClass("FishingArea");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Document");
		
		$fma=$this->getParam("fma");
		
		$query=new QueryCreator();
		$query->addSelect(" distinct t.id,t.limit_quantity,
						fa.fishing_area_name,org.trading_name,
						sum(r.received_quantity) as  received_quantity,
						sum(r.doa_quantity) as doa_quantity,
						sum(r.reject_quantity) as reject_quantity,
						o.name,o.moid,d.link ");
		
		$query->addFROM(Tac::_tablename." as t ");
		$query->addJoin(" LEFT JOIN ".FishingArea::_tablename." as fa on t.fishing_area_id=fa.fishing_area_id ");
		$query->addJoin(" LEFT Join ".Organization::_tablename." as org on fa.org_id=org.id ");
		$query->addJoin(" LEFT JOIN ".Receipt::_tablename." as r on r.supplier_org_id=org.id ");		
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as o on o.organism_id=t.organism_id ");
		$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.organism_id=r.org_organism_id ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." AS d ON d.id = o.picture_document_id ");
		if($fma)
			$query->addWhere(" fa.fishing_area_id =".$fma);
		$query->addWhere(" r.date_receipt>=t.date_start and 
						r.date_receipt<=t.date_expire and 
						oo.organism_id=t.organism_id
						group by t.id ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("inquiries_tac_table.tpl");
	}
	
	//Inq Staff
	public function inquiriesSIAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" 1=1 ");
		$this->assign("orgtypes",$orgtypes->toArray());
		
		$this->setNoLayout();
		$this->render("inquiries_staff.tpl");
	}
	public function inqSICountriesAction(){
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Countries");
		
		$type=$this->getParam("entity_type");
		if($type){
			
			$query=new QueryCreator();
			$query->addSelect(" distinct c.country_code,c.country ");
			$query->addFrom(Organization::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." as a on a.address_id=o.primary_address_id ");
			$query->addJoin(" LEFT JOIN ".Country::_tablename." as c on a.country_code=c.country_code ");
			$query->addWhere(" o.org_type='".$type."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['country_code'])
					$option.='<option value="'.$row['country_code'].'" >'.$row['country'].'</option>';
			}
		}
		
		echo '
			<select name="country" id="country" onChange="doajaxAction(\'/admin/inqSIentity/country/\'+document.MainQuery.country.value,0,\'entity\')">
				<option value=""> --- </option>
				'.$option.'
			</select>';
	}
	public function inqSIEntityAction(){
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Organization");
		
		$country=$this->getParam("country");
		if($country){
			$query=new QueryCreator();
			$query->addSelect(" distinct o.org_id,o.trading_name ");
			$query->addFrom(Address::_tablename." as a ");
			$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o on o.primary_address_id=a.address_id");
			$query->addWhere(" a.country_code='".$country."' ");
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['org_id'])
					$option.='<option value="'.$row['org_id'].'" >'.$row['trading_name'].'</option>';
			}
		}
		
		echo '
			<select name="entity" id="entity">
				<option value=""> --- </option>
				'.$option.'
			</select>';
	}
	public function inqSIShowListAction(){
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("Staff");
		Zend_loader::loadClass("StaffSkill");
		Zend_loader::loadClass("Skill");
		Zend_loader::loadClass("StaffTraining");
		Zend_loader::loadClass("Training");
		
		$entity=$this->getParam("entity");
		if($entity){
			$query=new QueryCreator();
			$query->addSelect(" s.staff_id,s.first_name,s.last_name,s.middle_name,s.position,s.date_hired,s.date_resigned,s.remarks,d.link ");
			$query->addFrom(Staff::_tablename." as s ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=s.photo_document_id ");
			$query->addWhere(" org_id=".$entity);
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$main = $db->executeQuery($query->createSQL());
			foreach($main as $key => $staff){
				$query=new QueryCreator();
				$query->addSelect(" st.staff_training_id,st.default_trainor,st.date_default_trained,st.actual_trainor,st.date_actual_trained,t.course_title ");
				$query->addFrom(StaffTraining::_tablename." as st ");
				$query->addJoin(" LEFT JOIN ".Training::_tablename." as t on st.training_id=t.id ");
				$query->addWhere(" st.staff_id='".$staff['staff_id']."' and t.date_training<=now() ");
				$db = DBCon::instance();
				//echo $query->createSQL();
				$history = $db->executeQuery($query->createSQL());
				
				$query=new QueryCreator();
				$query->addSelect(" ss.staff_skills_id,skills.skill,ss.level,ss.date_acquired ");
				$query->addFrom(StaffSkill::_tablename." as ss ");
				$query->addJoin(" LEFT JOIN ".Skill::_tablename." as skills on ss.skill_id=skills.id ");
				$query->addWhere(" ss.staff_id='".$staff['staff_id']."' ");
				$db = DBCon::instance();
				//echo $query->createSQL();
				$skill_and_competence = $db->executeQuery($query->createSQL());
				
				$query=new QueryCreator();
				$query->addSelect(" st.staff_training_id,skills.skill,t.course_title,t.trainor,t.date_training ");
				$query->addFrom(StaffTraining::_tablename." as st ");
				$query->addJoin(" LEFT JOIN ".Training::_tablename." as t on st.training_id=t.id ");
				$query->addJoin(" LEFT JOIN ".Skill::_tablename." as skills on t.skill_id=skills.id ");
				$query->addWhere(" st.staff_id='".$staff['staff_id']."' ");
				$query->addWhere(" isnull(st.date_actual_trained) ");
				$db = DBCon::instance();
				//echo $query->createSQL();
				$schedule_trainings = $db->executeQuery($query->createSQL());
				
				$main[$key]['history']=$history;
				$main[$key]['skill']=$skill_and_competence;
				$main[$key]['schedule_trainings']=$schedule_trainings;
			}
		}
		
		$this->assign("results",$main);
		$this->setNoLayout();
		$this->render("inquiries_staff_table.tpl");
	}
	
	//Inq WEP
	public function inquiriesWEMAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		//$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		Zend_loader::loadClass("OrganizationTypes");
		$orgtypes=new OrganizationTypes;
		$orgtypes->loadWhere(" 1=1 ");
		$this->assign("orgtypes",$orgtypes->toArray());
		
		$this->setNoLayout();
		$this->render("inquiries_water.tpl");
	}
	public function inqWEMCountriesAction(){
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Country");
		$type=$this->getParam("entity_type");
		if($type){
			
			$query=new QueryCreator();
			$query->addSelect(" distinct c.country_code,c.country ");
			$query->addFrom(Organization::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." as a on a.address_id=o.primary_address_id ");
			$query->addJoin(" LEFT JOIN ".Country::_tablename." as c on a.country_code=c.country_code ");
			$query->addWhere(" o.org_type='".$type."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['country_code'])
					$option.='<option value="'.$row['country_code'].'" >'.$row['country'].'</option>';
			}
		}
		
		echo '
			<select name="country" id="country" onChange="doajaxAction(\'/admin/inqWEMentity/country/\'+document.MainQuery.country.value,0,\'entity\')">
				<option value=""> --- </option>
				'.$option.'
			</select>
				';
	}
	public function inqWEMEntityAction(){
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Organization");
		
		$country=$this->getParam("country");
		if($country){
			$query=new QueryCreator();
			$query->addSelect(" distinct o.org_id,o.trading_name ");
			$query->addFrom(Address::_tablename." as a ");
			$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o on o.primary_address_id=a.address_id");
			$query->addWhere(" a.country_code='".$country."' ");
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['org_id'])
					$option.='<option value="'.$row['org_id'].'" >'.$row['trading_name'].'</option>';
			}
		}
		
		echo '
			<select name="entity" id="entity" onChange="doajaxAction(\'/admin/inqWEMTank/entity/\'+document.MainQuery.entity.value,0,\'tank\')">
				<option value=""> --- </option>
				'.$option.'
			</select>
				';
	}
	public function inqWEMTankAction(){
		$entity=$this->getParam("entity");
		if($entity){
			//tanks
			Zend_loader::loadClass("Tanks");
			$tanks=new Tanks();
			$tanks->loadWhere(" org_id=".$entity, array("name"));
			foreach($tanks->toArray() as $tank){
				$option.='<option value="'.$tank['tank_id'].'">'.
					$tank['name'].'</option>';
			}
		}
		
		echo '	<select name="tank" id="tank">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	public function inqWEMShowListAction(){		
		$tank=$this->getParam("tank");
		if($tank){
			//tanks inspections
			Zend_loader::loadClass("TankInspections");
			$tis=new TankInspections();
			$tis->loadWhere(" tank_id=".$tank, array("date_inspected"));
			$this->assign("results",$tis->toArray());
		}
		$this->setNoLayout();
		$this->render("inquiries_water_table.tpl");
	}
	
	
	public function reportsAction(){
		die("reports admin");
	}
	
	public function dataAdminAction(){
		die("data admin");
	}
	
	//OM
	public function dataOMAction(){
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->assign("menuUtility" ,$menuUtility);
		$db = DBCon::instance();
		
		/*$query = new QueryCreator();
		$query->addSelect(" o.organism_id as id,o.name,o.scientific_name,og.organism_group,
							o.date_created,o.date_updated,o.mac_certified,o.unsuitable_species,
							d.link ");
		$query->addFrom(" projecte_inventory.organisms as o ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organism_groups as og on o.organism_group_id=og.organism_group_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.documents as d on o.picture_document_id=d.id ");
		//$query->addLimit(" 0,20 ");
		
		//$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		Zend_loader::loadClass("Organism");
		$rowcount=$db->executeQuery("SELECT count(*) as value FROM ".Organism::_tablename);
		$this->assign("rowcount",$rowcount[0]['value']);
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		$this->setNoLayout();
		$this->render("dataOM.tpl");	
	}
	public function dataOMPagingAction(){
		die("asdfasdf");
	}
	public function dataOMEditAction(){
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("OrganizationOrganismBan");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("EmailMessage");
		Zend_loader::loadClass("Document");
		//organism group
		Zend_loader::loadClass("OrganismGroups");
		$orgs=new OrganismGroups();
		$orgs->loadWhere("1 = 1",array("organism_group"));
		$this->assign("organism_groups",$orgs->toArray());
		
		//countries
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1", "country");
		$this->assign("countries",$countries->toArray());
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1",array("name"));
		$this->assign("currencies",$currencies->toArray());
		
		//organization organism banned
		$query=new QueryCreator();
		$query->addSelect(" b.*,oo.org_id,oo.organism_id,e.message,o.name ");
		$query->addFrom(OrganizationOrganismBan::_tablename." as b ");
		$query->addJoin(" left join ".OrganizationOrganism::_tablename." oo on oo.org_organism_id=b.org_organism_id ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.org_id=oo.org_id ");
		$query->addJoin(" left join ".EmailMessage::_tablename." as e on e.id=b.message_id ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("banlist",$result);
		
		$id=$this->getParam("id");
		if($id){
			$query = new QueryCreator();
			$query->addSelect(" o.organism_id,o.moid,o.name,o.scientific_name,o.description,o.sale_unit,
							o.organism_group_id,o.default_inventory_cost,o.default_base_price_sale,
							o.currency_mark,o.country_code,o.province,d.link,
							o.mac_certified, o.unsuitable_species ");
			$query->addFrom(Organism::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=o.picture_document_id ");
			$query->addWhere(" o.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("result",$result);
		}
		
		$this->setNoLayout();
		$this->render("dataOM_popup.tpl");	
	}
	public function dataOMAddOrgAction(){
		$auth = BaseAuth::getInstance();
		$id=$this->getParam("organism_id");
		Zend_loader::loadClass("Organism");
		$org=new Organism();
		if(empty($id)){
			$org->date_created=date("Y-m-d H:i:s");
			$org->created_by = $auth->id;
		}
		else{
			$org->load($id);
			$org->date_updated=date("Y-m-d H:i:s");
			$org->updated_by = $auth->id;
		}
		
		$pictureInput = $_FILES['photo'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['photo']['tmp_name'], $uploadDIR.'/'.$fileName);
			
			Zend_Loader::loadClass("Document");
			$doc=new Document();
			if($org->picture_document_id)
				$doc->load($org->picture_document_id);
			$doc->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$doc->title	=	$fileName;
			$doc->description = "organism pic";
			$doc->date_created = date("Y-m-d H:i:s");
			$doc->created_by = $auth->username;
			$doc->save();
			
			$org->picture_document_id=$doc->id;
		}
		$org->moid=$this->getParam("moid");
		$org->name=$this->getParam("name");
		$org->scientific_name=$this->getParam("sname");
		$org->description=$this->getParam("description");
		$org->sale_unit=$this->getParam("sale_unit");
		$org->organism_group_id=$this->getParam("organism_group");
		$org->default_inventory_cost=$this->getParam("DIC");
		$org->default_base_price_sale=$this->getParam("DBPS");
		$org->currency_mark=$this->getParam("currency_mark");
		$org->country_code=$this->getParam("country_code");
		$org->province=$this->getParam("province");
		$org->mac_certified=$this->getParam("mac_certified");
		$org->unsuitable_species=$this->getParam("u_species");
		
		$org->save();
		$org->organism_id=$org->id;
		$org->save();
		//$this->_redirect("/admin/dataOMRefresh");
		$this->dataOMRefreshAction();
	}
	public function dataOMSaveListChangesAction(){
		Zend_loader::loadClass("Organisms");
		Zend_loader::loadClass("Organism");
		$orgs=new Organisms();
		$orgs->loadWhere(" 1=1 ");
		$o=new Organism();
		foreach($orgs as $org){
			$o->clear();
			if($this->getParam('del_'.$org->organism_id)){
				$o->load($org->organism_id);
				$o->delete(TRUE);
				continue;
			}
			$o->load($org->organism_id);
			
			$o->mac_certified=$this->getParam('mac_'.$org->organism_id);
			$o->unsuitable_species=$this->getParam('uns_'.$org->organism_id);
			$o->save();
		}
		
		//$this->_redirect("/admin/dataOMRefresh");
		$this->dateOMRefreshAction();
	}
	public function dataOMRefreshAction(){
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->assign("menuUtility" ,$menuUtility);
		
		/*$query = new QueryCreator();
		$query->addSelect(" o.organism_id as id,o.name,o.scientific_name,og.organism_group,
							o.date_created,o.date_updated,o.mac_certified,o.unsuitable_species,
							d.link ");
		$query->addFrom(" projecte_inventory.organisms as o ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organism_groups as og on o.organism_group_id=og.organism_group_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.documents as d on o.picture_document_id=d.id ");
		//$query->addLimit(" 0,20 ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		Zend_loader::loadClass("Organism");		
		$rowcount=$db->executeQuery("SELECT count(*) as value FROM ".Organism::_tablename);
		print $rowcount;die();
		$this->assign("rowcount",$rowcount[0]['value']);
		
		$this->setLayout("admin");
		$this->render("dataOM.tpl");	
	}
	public function dataOMEditRefreshAction(){
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		
		Zend_loader::loadClass("OrganizationOrganismBan");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("EmailMessage");
		Zend_loader::loadClass("Document");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->assign("menuUtility" ,$menuUtility);
		
		//organism group
		Zend_loader::loadClass("OrganismGroups");
		$orgs=new OrganismGroups();
		$orgs->loadWhere("1 = 1",array("organism_group"));
		$this->assign("organism_groups",$orgs->toArray());
		
		//countries
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1", "country");
		$this->assign("countries",$countries->toArray());
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1",array("name"));
		$this->assign("currencies",$currencies->toArray());
		
		//organization organism banned
		$query=new QueryCreator();
		$query->addSelect(" b.*,oo.org_id,oo.organism_id,e.message,o.name ");
		$query->addFrom(OrganizationOrganismBan::_tablename." as b ");
		$query->addJoin(" left join ".OrganizationOrganism::_tablename." oo on oo.org_organism_id=b.org_organism_id ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.org_id=oo.org_id ");
		$query->addJoin(" left join ".EmailMessage::_tablename." as e on e.id=b.message_id ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("banlist",$result);
		
		//$id=$this->getParam("id");
		$cart = CartUtility::getadminCart();
		$id=$cart->id;
		if($id){
			$query = new QueryCreator();
			$query->addSelect(" o.organism_id,o.moid,o.name,o.scientific_name,o.description,o.sale_unit,
							o.organism_group_id,o.default_inventory_cost,o.default_base_price_sale,
							o.currency_mark,o.country_code,o.province,d.link,
							o.mac_certified, o.unsuitable_species ");
			$query->addFrom(Organism::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=o.picture_document_id ");
			$query->addWhere(" o.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("result",$result);
		}
		
		$this->setLayout("admin");
		$this->render("dataOM_popup.tpl");	
	}
	public function dataOMSaveBanListChangesAction(){
		Zend_loader::loadClass("OrganizationOrganismBan");
		Zend_loader::loadClass("OrganizationOrganism");
		
		$organism_id=$this->getParam("organism_id");
		$query=new QueryCreator();
		$query->addSelect(" oob.id ");
		$query->addFrom(OrganizationOrganismBan::_tablename." as oob ");
		$query->addJoin(" left join ".OrganizationOrganism." as oo on oob.org_organism_id=oo.org_organism_id ");
		$query->addWhere(" oo.organism_id=".$organism_id);

		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("OrganizationOrganismBan");
		$oob=new OrganizationOrganismBan();
		
		foreach($result as $row){
			if($this->getParam('del_'.$row['id'])){
				$oob->clear();
				$oob->load($row['id']);
				$oob->delete(TRUE);
			}
		}
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$organism_id;
		
		//$this->_redirect("/admin/dataOMEditRefresh/id/".$organism_id);
		$this->dataOMEditRefreshAction();
	}
	public function dataOMEditBanAction(){
		Zend_loader::loadClass("OrganizationOrganismBan");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("EmailMessage");
		$organism_id=$this->getParam("organism_id");
		$id=$this->getParam("id");
		
		if($id){
			$query=new QueryCreator();
			$query->addSelect(" b.*,oo.org_id,oo.organism_id,e.message ");
			$query->addFrom(OrganizationOrganismBan::_tablename." as b ");
			$query->addJoin(" left join ".OrganizationOrganism::_tablename." oo on oo.org_organism_id=b.org_organism_id ");
			$query->addJoin(" left join ".EmailMessage::_tablename." as e on e.id=b.message_id ");
			$query->addWhere(" b.id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("ban",$result[0]);
		}
		$this->assign("organism_id",$organism_id);
		
		//Organizations
		/*Zend_loader::loadClass("Organizations");
		$orgs=new Organizations();
		$orgs->loadWhere(" 1=1 ");
		$this->assign("orgs",$orgs->toArray());*/

		$query=new QueryCreator();
		$query->addSelect(" o.id,o.name ");
		$query->addFrom(OrganizationOrganism::_tablename." as oo ");
		$query->addJoin(" left join ".Organization::_tablename." as o on oo.org_id=o.id ");
		$query->addWhere(" oo.organism_id=".$organism_id);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$orgs = $db->executeQuery($query->createSQL());
		$this->assign("orgs",$orgs);
			
		$this->setNoLayout();
		$this->render("dataOMBan_popup.tpl");
	}
	public function dataOMAddBanAction(){
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$id			=$this->getParam("hid");
		$organism_id=$this->getParam("organism_id");
		Zend_loader::loadClass("Organism");
		$organism=new Organism();
		$organism->load($organism_id);
		
		Zend_loader::loadClass("OrganizationOrganismBan");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("EmailMessage");
		$oob=new OrganizationOrganismBan();
		$oo=new OrganizationOrganism();
		$email=new EmailMessage();
		if($id){
			$oob->load($id);
			$oo->load($oob->org_organism_id);
			$email->load($oob->message_id);
			
			$oob->date_updated 	= date("Y-m-d H:i:s");
			$oob->created_by	= $auth->id;
			
		}
		else{
			$oob->date_created  = date("Y-m-d H:i:s");
			$oob->created_by	= $auth->id;
			
			$email->date_created	= date("Y-m-d H:i:s");
			$email->created_by	= $auth->id;
			
			//try to find org_organism
			$oo->loadWhere(" org_id=".$this->getParam("organization")." 
						 AND organism_id=".$organism_id);
		}
		//email
		$email->source_org_id 	= $auth->org_id;
		$email->recipient_org_id= $this->getParam("organization");
		$email->message			= $this->getParam("message");
		$email->date_sent		= date("Y-m-d H:i:s");
		$email->subject			= "Organism Banlist -".$organism->name;
		$email->save();
		$email->message_id		= $email->id;
		$email->save();
		
		//oob
		$oob->org_organism_id	= $oo->org_organism_id;
		$oob->date_from			= date("Y-m-d H:i:s",strtotime($this->getParam("f_date")));
		$oob->date_to			= date("Y-m-d H:i:s",strtotime($this->getParam("t_date")));
		$oob->message_id		= $email->id;
		$oob->save();
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$organism_id;
		
		//$this->_redirect("/admin/dataOMEditRefresh/id/".$organism_id);
		$this->dataOMEditRefreshAction();
	}
	
	//OM search
	public function dataOMProvinceAction(){
		Zend_loader::loadClass("Address");
		$country=$this->getParam("country");
		if($country){
			$query=new QueryCreator();
			$query->addSelect(" Distinct a.province ");
			$query->addFrom(Address::_tablename." as a ");
			$query->addWhere(" a.country_code='".$country."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['province'])
					$option.='<option value="'.$row['province'].'" >'.$row['province'].'</option>';
			}
		}
		
		echo '
					<select name="province" id="province" onChange="doajaxAction(\'/admin/dataOMFMA/country/\'+document.searchForm.country.value+\'/province/\'+document.searchForm.province.value,0,\'fma\')">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	public function dataOMFMAAction(){
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Organization");
		
		$country=$this->getParam("country");
		$province=$this->getParam("province");
		if($country AND $province){
			
			$query=new QueryCreator();
			$query->addSelect(" DISTINCT o.org_id, o.trading_name ");
			$query->addFrom(Address::_tablename." AS a ");
			$query->addJoin(" LEFT JOIN ".Organization::_tablename." AS o ON o.primary_address_id = a.address_id ");
			$query->addWhere(" a.province = '".$province."'
								AND a.country_code='".$country."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['org_id'])
					$option.='<option value="'.$row['org_id'].'" >'.$row['trading_name'].'</option>';
			}
		}
		
		echo '
			<select name="fma" id="fma" >
				<option value=""> --- </option>
				'.$option.'
			</select>';
	}
	public function dataOMSearchAction(){
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("OrganismGroup");
		Zend_loader::loadClass("OrganizationOrganism");
		
		$country=$this->getParam("country");
		$fma=$this->getParam("fma");
		$group=$this->getParam("group");
		$names=$this->getParam("names");
		
		$query=new QueryCreator();
		$query->addSelect(" o.organism_id as id,o.name,o.scientific_name,og.organism_group,
							o.date_created,o.date_updated,o.mac_certified,o.unsuitable_species,
							d.link ");
		$query->addFrom(Organism::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".OrganismGroup::_tablename." as og on o.organism_group_id=og.organism_group_id ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on o.picture_document_id=d.id ");
		
		if($fma){
			$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo ON oo.organism_id=o.organism_id ");
			$query->addWhere(" oo.org_id=".$fma);
		}
		if($country) {
			$query->addWhere(" o.country_code = '".$country."'");}
		if($group) {
			$query->addWhere(" o.organism_group_id=".$group);}
		if($names) {
			$query->addWhere(" o.name like '%".$names."%' OR o.scientific_name like '%".$names."%' ");}
			
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("dataOM_table.tpl");
	}
	//OM excel
	public function dataOMExportAction(){
		//export to excel
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("OrganismGroup");
		// typically this will be generated/read from a database table
		$query = new QueryCreator();
		$query->addSelect(" o.organism_id,o.moid,o.name,o.scientific_name,o.description,o.sale_unit,
							o.organism_group_id,og.organism_group,o.default_inventory_cost,
							o.default_base_price_sale,o.currency_mark,o.country_code,o.province,
							o.mac_certified,o.unsuitable_species");
		$query->addFrom(Organism::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".OrganismGroup::_tablename." as og on o.organism_group_id=og.organism_group_id ");
		//$query->addLimit(" 0,20 ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		$query = new QueryCreator();
		$query->addSelect(" organism_group_id,organism_group ");
		$query->addFrom(OrganismGroup::_tablename." as o ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result2 = $db->executeQuery($query->createSQL());
		
		$workbook = new Spreadsheet_Excel_Writer();
 
		/* Sending HTTP headers, this will popup a file save/open
		dialog in the browser when this file is run
		*/
		$workbook->send('Organisms'.time().'.xls');
		 
		// Create worksheets
		$worksheet1 =& $workbook->addWorksheet('Organisms');
		$worksheet2 =& $workbook->addWorksheet('Organism Groups');
				
		// Set header formating for Sheet 1
		$header =& $workbook->addFormat();
		$header->setBold();		// Make it bold
		$header->setColor('white');	// Make foreground color white
		$header->setFgColor("blue");	// Set background color to blue
		$header->setHAlign('center');	// Align text to center
		 
		// Write some data on Sheet 1
		 
		$worksheet1->write(0, 0, 'Organism_ID', $header);
		$worksheet1->write(0, 1, 'MOID', $header);
		$worksheet1->write(0, 2, 'Name', $header);
		$worksheet1->write(0, 3, 'Scientific Name', $header);
		$worksheet1->write(0, 4, 'Description', $header);
		$worksheet1->write(0, 5, 'Sale Unit', $header);
		$worksheet1->write(0, 6, 'Organism_Group_ID', $header);
		$worksheet1->write(0, 7, 'Organism Group', $header);
		$worksheet1->write(0, 8, 'Default Inventory Cost', $header);
		$worksheet1->write(0, 9, 'Default Base Price Sale', $header);
		$worksheet1->write(0, 10, 'Currency Mark', $header);
		$worksheet1->write(0, 11, 'Country Code', $header);
		$worksheet1->write(0, 12, 'Province', $header);
		$worksheet1->write(0, 13, 'Mac Certified', $header);
		$worksheet1->write(0, 14, 'Unsuitable Species', $header);
		
		$y=1;
		foreach($result as $row){
			$x=0;
			foreach($row as $count){
				$worksheet1->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 2
		$worksheet2->write(0, 0, 'Organism_Group_ID', $header);
		$worksheet2->write(0, 1, 'Organism_Group', $header);
		$y=1;
		foreach($result2 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet2->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		// Send the file to the browser
		$workbook->close();
	}
	public function dataOMImportAction(){
		if ( $_FILES['file']['tmp_name'] )
		{
			$auth = BaseAuth::getInstance();
			$data=new Spreadsheet_Excel_Reader();
			$data->setUTFEncoder('iconv');
			$data->setOutputEncoding('UTF-8');
			$data->read($_FILES['file']['tmp_name']);
			
			Zend_loader::loadClass("Organism");
			 for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
				$org=new Organism();
				$id							=$data->sheets[0]['cells'][$i][1];
				if(empty($id)){
					$org->date_created=date("Y-m-d H:i:s");
					$org->created_by = $auth->id;
				}
				else{
					$org->load($id);
					$org->date_updated=date("Y-m-d H:i:s");
					$org->updated_by = $auth->id;
				}
				
				$org->moid					=$data->sheets[0]['cells'][$i][2];
				$org->name					=$data->sheets[0]['cells'][$i][3];
				$org->scientific_name		=$data->sheets[0]['cells'][$i][4];
				$org->description			=$data->sheets[0]['cells'][$i][5];
				$org->sale_unit				=$data->sheets[0]['cells'][$i][6];
				$org->organism_group_id		=$data->sheets[0]['cells'][$i][7];
				$org->default_inventory_cost =$data->sheets[0]['cells'][$i][9];
				$org->default_base_price_sale=$data->sheets[0]['cells'][$i][10];
				$org->sale_unit				=$data->sheets[0]['cells'][$i][11];
				$org->currency_mark			=$data->sheets[0]['cells'][$i][12];
				$org->country_code			=$data->sheets[0]['cells'][$i][13];
				$org->province				=$data->sheets[0]['cells'][$i][14];
				$org->mac_certified			=$data->sheets[0]['cells'][$i][15];
				$org->unsuitable_species	=$data->sheets[0]['cells'][$i][16];
				$org->updated_by 			=$auth->username;
				$org->save();
				
				$org->organism_id=$org->id;
				$org->save();
			}
		}
		//$this->_redirect("/admin/dataOMRefresh");
		$this->dataOMRefreshAction();
	}

	//FMA
	public function dataFMAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();		
		$this->assign("menuUtility" ,$menuUtility);

		/*$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(" projecte_entity.organizations as o ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries as c ON a.country_code=c.country_code ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("dataFMA.tpl");	
	}
	public function dataFMAEditAction(){
		
		$id=$this->getParam("id");
		$this->assign("org_id",$id);
		
		//organization types
		Zend_loader::loadClass("OrganizationTypes");
		$orgTypes=new OrganizationTypes();
		$orgTypes->loadWhere(" 1=1 ");
		$orgTypes->toArray();
		$this->assign("organizationTypes",$orgTypes->toArray());
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere(" 1=1 ",array("name"));
		$this->assign("currencies",$currencies->toArray());
		
		//ship_methods
		Zend_loader::loadClass("Shipmethods");
		$shipmethods=new Shipmethods();
		$shipmethods->loadWhere(" 1=1 ",array("name"));
		$this->assign("ship_methods",$shipmethods->toArray());
		
		if(!empty($id)){
			//organization
			Zend_loader::loadClass("Organizations");
			$orgs=new Organizations();
			$orgs->loadWhere(" org_id = ".$id);
			$orgs->toArray();
			$this->assign("organization",$orgs->toArray());
			
			//organization addresses
			Zend_loader::loadClass("OrganizationAddress");
			Zend_loader::loadClass("Address");
			$query = new QueryCreator();
			$query->addSelect(" oa.org_address_id,oa.org_id,oa.address_id,oa.address_type, oa.inactive, a.address1, a.city, a.province, a.country_code ");
			$query->addFrom(OrganizationAddress::_tablename." AS oa ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." AS a ON oa.address_id = a.address_id ");
			$query->addWhere(" oa.org_id =".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$org_addresses = $db->executeQuery($query->createSQL());
			$this->assign("org_addresses",$org_addresses);
			
			//organizattion contacts
			Zend_loader::loadClass("OrganizationContact");
			Zend_loader::loadClass("Contact");
			$query = new QueryCreator();
			$query->addSelect(" oc.org_id,oc.org_contact_id,oc.contact_id,oc.inactive,c.name,c.remarks,c.telephone_no,c.email ");
			$query->addFrom(OrganizationContact::_tablename." as oc ");
			$query->addJoin(" LEFT join ".Contact::_tablename." as c on c.contact_id=oc.contact_id ");
			$query->addWhere(" oc.org_id =".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$org_contacts = $db->executeQuery($query->createSQL());
			$this->assign("org_contacts",$org_contacts);
		
			//fishers
			Zend_loader::loadClass("FishingArea");
			Zend_loader::loadClass("FishingAreas");
			Zend_loader::loadClass("Document");
			Zend_loader::loadClass("Fisher");
			$query = new QueryCreator();
			$query->addSelect(" f.id,f.first_name,f.last_name,f.fisher_id,fa.fishing_area_name,d.link ");
			$query->addFrom(FishingArea::_tablename." as fa ");
			$query->addJoin(" LEFT JOIN ".Fisher::_tablename." as f on f.fishing_area_id=fa.fishing_area_id ");
			$query->addJoin(" LEFT JOIN ".Contact::_tablename." as c on f.contact_id=c.contact_id ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=c.photo_document_id ");
			$query->addWhere(" fa.org_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$fishers = $db->executeQuery($query->createSQL());
			$this->assign("fishers",$fishers);
			
			//fishing area
			$fishing_areas = new FishingAreas();
			$fishing_areas->loadWhere("org_id=".$id);
			$this->assign("fishing_areas",$fishing_areas->toArray());
		}
		
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->org_maintenance_type=$this->getParam("org_maintenance_type");
		
		$this->assign("org_maintenance_type",$this->getParam("org_maintenance_type"));
		$this->setNoLayout();
		$this->render("dataFMA_popup.tpl");	
	}
	public function dataFMASaveOrgAction(){
		$org_id=$this->getParam("org_id");
		$auth = BaseAuth::getInstance();
		//org
		Zend_loader::loadClass("Organization");
		$org=new Organization();
		
		if($org_id){
			$org->load($org_id);
			$org->date_updated=date("Y-m-d H:i:s"); 
			$org->updated_by=$auth->id;
		}else{
			$org->date_created=date("Y-m-d H:i:s");
			$org->created_by=$auth->id;
		}
		
		$org->name				=$this->getParam("name");
		$org->trading_name		=$this->getParam("trading_name");
		$org->phone				=$this->getParam("phone");
		$org->fax				=$this->getParam("fax");
		$org->email				=$this->getParam("email");
		$org->website			=$this->getParam("website");
		$org->inactive			=$this->getParam("inactive");
		$org->non_taxable		=$this->getParam("non_taxable");
		$org->org_type			=$this->getParam("organization_type");
		$org->notes				=$this->getParam("notes");
		$org->credit_limit		=$this->getParam("credit_limit");
		$org->ship_method		=$this->getParam("ship_method");
		$org->tax_id_number		=$this->getParam("tax_id_number");
		$org->bank_name			=$this->getParam("bank_name");
		$org->bank_account_number=$this->getParam("bank_account_number");
		$org->currency_mark		=$this->getParam("currency_mark");
		$org->save();
		
		$org->org_id=$org->id;
		$org->save();
		
		$org_maintenance_type	=$this->getParam("org_maintenance_type");
		if($org_maintenance_type=='dataFM'){
			//$this->_redirect("/admin/dataFMARefresh");
			$this->dataFMARefreshAction();
		}
		else{
			//$this->_redirect("/admin/dataOFMARefresh");
			$this->dataOFMARefreshAction();
		}
	}
	public function dataFMAEditOrgAddressAction(){
		
		//countries
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1", "country");
		$this->assign("countries",$countries->toArray());
		
		$org_address_id=$this->getParam("id");
		$org_id=$this->getParam("org_id");
		$address_id=$this->getParam("address_id");
		$this->assign("org_address_id",$org_address_id);
		$this->assign("org_id",$org_id);
		$this->assign("address_id",$address_id);
		
		if(!empty($org_address_id)){
			Zend_loader::loadClass("OrganizationAddress");
			Zend_loader::loadClass("Address");
			$query = new QueryCreator();
			$query->addSelect(" oa.org_id,oa.org_address_id,oa.address_id,oa.address_type,oa.inactive,a.address1,
								a.city,a.province,a.country_code,a.zip ");
			$query->addFrom(OrganizationAddress::_tablename." as oa ");
			$query->addJoin(" left join ".Address::_tablename." as a on oa.address_id=a.address_id ");
			$query->addWhere(" oa.org_address_id=".$org_address_id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$address = $db->executeQuery($query->createSQL());
			$this->assign("result",$address);
		}		
		
		$this->setNoLayout();
		$this->render("dataFMAOrgAddress.tpl");	
	}
	public function dataFMAAddressSaveAction(){
		$auth = BaseAuth::getInstance();
		$address_id=$this->getParam("address_id");
		$org_id=$this->getParam("org_id");
		$org_address_id=$this->getParam("org_address_id");
		
		//address
		Zend_loader::loadClass("Address");
		$address=new Address();
		if(!empty($address_id)){
			$address->load($address_id);
			$address->date_update=date("Y-m-d H:i:s");
			$address->updated_by=$auth->id;
		}
		else{
			$address->date_created=date("Y-m-d H:i:s");
			$address->created_by=$auth->id;
		}
		$address->address1	=	$this->getParam("address1");
		$address->city		=	$this->getParam("city");
		$address->province	= 	$this->getParam("province");
		$address->country_code= $this->getParam("country_code");
		$address->zip		=	$this->getParam("zip");
		$address->save();
		
		//organization address
		Zend_loader::loadClass("OrganizationAddress");
		$orgaddress=new OrganizationAddress();
		if(!empty($org_address_id)){
			$orgaddress->load($org_address_id);
			$orgaddress->date_update=date("Y-m-d H:i:s");
			$orgaddress->updated_by=$auth->username;
		}
		else{
			$orgaddress->address_id	=$address->address_id;
			$orgaddress->org_id		=$org_id;
			$orgaddress->date_created=date("Y-m-d H:i:s");
			$orgaddress->created_by=$auth->username;
		}
		$orgaddress->address_type	=	$this->getParam("address_type");
		$orgaddress->inactive		=	$this->getParam("inactive");
		$orgaddress->save();
		
		$orgaddress->org_address_id=$orgaddress->id;
		$orgaddress->save();
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	public function dataFMAEditOrgContactAction(){
		$org_contact_id	=$this->getParam("id");
		$org_id			=$this->getParam("org_id");
		$contact_id		=$this->getParam("contact_id");
		$this->assign("org_contact_id",$org_contact_id);
		$this->assign("org_id",$org_id);
		$this->assign("contact_id",$contact_id);
		if(!empty($org_contact_id)){
			Zend_loader::loadClass("OrganizationContact");
			Zend_loader::loadClass("Contact");
			Zend_loader::loadClass("Document");
			$query=new QueryCreator();
			$query->addSelect(" oc.org_contact_id,oc.org_id,oc.contact_id,oc.inactive,
								c.*,p.link as photo,f.link as fingerprint ");
			$query->addFrom(OrganizationContact::_tablename." as oc ");
			$query->addJoin(" left join ".Contact::_tablename." as c on c.contact_id=oc.contact_id ");
			$query->addJoin(" left join ".Document::_tablename." as p on c.photo_document_id=p.id ");
			$query->addJoin(" left join ".Document::_tablename." as f on c.fingerprint_document_id=f.id ");
			$query->addWhere(" oc.org_contact_id=".$org_contact_id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$contacts = $db->executeQuery($query->createSQL());
			$this->assign("result",$contacts);
		}
		
		$this->setNoLayout();
		$this->render("dataFMAOrgContact.tpl");	
	}
	public function dataFMAContactSaveAction(){
		//print_r($this->getParam());die();
		$auth = BaseAuth::getInstance();
		$contact_id=$this->getParam("contact_id");
		$org_id=$this->getParam("org_id");
		$org_contact_id=$this->getParam("org_contact_id");
		
		//contact
		Zend_loader::loadClass("Contact");
		$contact=new Contact();
		
		Zend_loader::loadClass("Document");
		$photo=new Document();
		$fingerprint=new Document();
		if(!empty($contact_id)){
			$contact->load($contact_id);
			$contact->date_updated=date("Y-m-d H:i:s");
			$contact->updated_by=$auth->id;
			
			$photo->load($contact->photo_document_id);
			$photo->date_updated=date("Y-m-d H:i:s");
			$photo->updated_by=$auth->id;
			
			$fingerprint->load($contact->fingerprint_document_id);
			$fingerprint->date_updated=date("Y-m-d H:i:s");
			$fingerprint->updated_by=$auth->id;
		}
		else{
			$photo->date_created=date("Y-m-d H:i:s");
			$photo->created_by=$auth->id;
			
			$fingerprint->date_created=date("Y-m-d H:i:s");
			$fingerprint->created_by=$auth->id;
			
			$contact->date_created=date("Y-m-d H:i:s");
			$contact->created_by=$auth->id;
		}
		//photo
		$pictureInput = $_FILES['photo'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['photo']['tmp_name'], $uploadDIR.'/'.$fileName);
			
			Zend_Loader::loadClass("Document");
			$pic=new Document();
			if(!empty($contact->photo_document_id))
				$pic->load($contact->photo_document_id);
			$pic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$pic->title	=	"Photo Pic";
			$pic->save();
			$contact->photo_document_id=$pic->id;
		}
		
		//fingerprint
		$pictureInput = $_FILES['fingerprint'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['fingerprint']['tmp_name'], $uploadDIR.'/'.$fileName);
		
			Zend_Loader::loadClass("Document");
			$fpic=new Document();
			if($contact->fingerprint_document_id)
				$fpic->load($contact->fingerprint_document_id);
			$fpic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$fpic->title	=	"Fingerprint Pic";
			$fpic->save();
			$contact->fingerprint_document_id=$fpic->id;
		}
		//contact
		$contact->name				=$this->getParam("name");
		$contact->remarks			=$this->getParam("remarks");
		$contact->telephone_no		=$this->getParam("telephone_no");
		$contact->fax_no			=$this->getParam("fax_no");
		$contact->email				=$this->getParam("email");
		$contact->birth_date		=date("Y-m-d H:i:s",strtotime($this->getParam("b_date")));
		$contact->license_date		=date("Y-m-d H:i:s",strtotime($this->getParam("l_date")));
		$contact->blood_group		=$this->getParam("blood_group");
		$contact->height			=$this->getParam("height");
		$contact->save();

		//organization contact
		Zend_loader::loadClass("OrganizationContact");
		$org_contact=new OrganizationContact();
		if(!empty($org_contact_id)){
			$org_contact->load($org_contact_id);
			$org_contact->date_updated=date("Y-m-d H:i:s");
			$org_contact->updated_by=$auth->id;
		}
		else{
			$org_contact->date_created=date("Y-m-d H:i:s");
			$org_contact->created_by=$auth->id;
		}
		$org_contact->org_id=$org_id;
		$org_contact->inactive=$this->getParam("inactive");
		$org_contact->contact_id=$contact->contact_id;
		$org_contact->save();
		
		$org_contact->org_contact_id=$org_contact->id;
		$org_contact->save();
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	public function dataFMAEditFisherAction(){
		$org_id	=$this->getParam("org_id");
		$fid		=$this->getParam("id");
		
		$this->assign("org_id",$org_id);
		
		Zend_loader::loadClass("Document");
		$pic=array();
		if($fid){
			Zend_loader::loadClass("Fisher");
			$fisher=new Fisher();
			$fisher->load($fid);
			$this->assign("fisher",$fisher->toArray());
			
			$tempdoc=new Document();
			if($fisher->signature_document_id)
				$tempdoc->load($fisher->signature_document_id);
			$pic['signature']=$tempdoc->link;
		}
		
		if($fisher->address_id){
			Zend_loader::loadClass("Address");
			$address=new Address();
			$address->load($fisher->address_id);
			$this->assign("address",$address->toArray());
		}
		
		if($fisher->contact_id){
			Zend_loader::loadClass("Contact");
			$contact=new Contact();
			$contact->load($fisher->contact_id);
			$this->assign("contact",$contact->toArray());
			
			$tempdoc=new Document();
			if($contact->photo_document_id)
				$tempdoc->load($contact->photo_document_id);
			$pic['photo']=$tempdoc->link;
			
			$tempdoc=new Document();
			if($contact->photo_document_id)
				$tempdoc->load($fisher->fingerprint_document_id);
			$pic['fingerprint']=$tempdoc->link;
		}
		
		$this->assign("pic",$pic);
		
		//countries
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		//fishing area
		Zend_loader::loadClass("FishingAreas");
		$fishing_areas=new FishingAreas();
		$fishing_areas->loadWhere(" org_id=".$org_id);
		$this->assign("fishing_areas",$fishing_areas->toArray());
		
		$this->setNoLayout();
		$this->render("dataFMAOrgFisher.tpl");	
	}
	public function dataFMAFisherSaveAction(){
		
		$auth = BaseAuth::getInstance();
		$id=$this->getParam("id");
		$org_id=$this->getParam("org_id");
		
		//fisher
		Zend_loader::loadClass("Fisher");
		
		//address
		Zend_loader::loadClass("Address");
		
		//contacts
		Zend_loader::loadClass("Contact");
		
		$fisher=new Fisher();
		$address=new Address();
		$contact=new Contact();
		
		if($id){
			$fisher->load($id);
			$address->load($fisher->address_id);
			$contact->load($fisher->contact_id);
			
			$fisher->date_updated=date("Y-m-d H:i:s");
			$fisher->updated_by=$auth->id;
		}
		else{
			$fisher->date_created=date("Y-m-d H:i:s");
			$fisher->created_by=$auth->id;
		}
		
		//address
		$address->address1	=	$this->getParam("address1");
		$address->city		=	$this->getParam("city");
		$address->province	= 	$this->getParam("province");
		$address->country_code= $this->getParam("country_code");
		$address->zip		=	$this->getParam("zip");
		$address->save();
		
		//fingerprint
		$pictureInput = $_FILES['fingerprint'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['fingerprint']['tmp_name'], $uploadDIR.'/'.$fileName);
		
			Zend_Loader::loadClass("Document");
			$fpic=new Document();
			if($contact->fingerprint_document_id)
				$fpic->load($contact->fingerprint_document_id);
			$fpic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$fpic->title	=	"Fingerprint Pic";
			$fpic->save();
			$contact->fingerprint_document_id=$fpic->id;
		}
		
		//photo
		$pictureInput = $_FILES['photo'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['photo']['tmp_name'], $uploadDIR.'/'.$fileName);
			
			Zend_Loader::loadClass("Document");
			$pic=new Document();
			if(!empty($contact->photo_document_id))
				$pic->load($contact->photo_document_id);
			$pic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$pic->title	=	"Photo Pic";
			$pic->save();
			$contact->photo_document_id=$pic->id;
		}
		
		//contact
		$contact->name				=$this->getParam("name");
		$contact->remarks			=$this->getParam("remarks");
		$contact->telephone_no		=$this->getParam("telephone_no");
		$contact->fax_no			=$this->getParam("fax_no");
		$contact->email				=$this->getParam("email");
		$contact->birth_date		=date("Y-m-d H:i:s",strtotime($this->getParam("b_date")));
		$contact->license_date		=date("Y-m-d H:i:s",strtotime($this->getParam("l_date")));
		$contact->blood_group		=$this->getParam("blood_group");
		$contact->height			=$this->getParam("height");
		$contact->save();

		//signature
		$pictureInput = $_FILES['signature'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['signature']['tmp_name'], $uploadDIR.'/'.$fileName);
		
			Zend_Loader::loadClass("Document");
			$spic=new Document();
			if($fisher->signature_document_id)
				$spic->load($fisher->signature_document_id);
			$spic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$spic->title	=	"Signature Pic";
			$spic->save();
			$fisher->signature_document_id=$spic->id;
		}
		
		//fisher
		$fisher->first_name			=$this->getParam("first_name");
		$fisher->last_name			=$this->getParam("last_name");
		$fisher->gender				=$this->getParam("gender");
		$fisher->address_id			=$address->address_id;
		$fisher->contact_id 		=$contact->contact_id;
		$fisher->country_code 		=$address->country_code;
		$fisher->fishing_area_id	=$this->getParam("fishing_area");
		$fisher->fisher_id 			=$this->getParam("fisher_id");
		$fisher->save();
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	
	public function dataFMAEditFishingAreaAction(){
		$org_id		=$this->getParam("org_id");
		$fid		=$this->getParam("area_id");
		
		Zend_loader::loadClass("FishingArea");
		$fishing_area	=	new FishingArea();
		$fishing_area->load($fid);
		$this->assign("fishing_area",$fishing_area->toArray());
		
		Zend_loader::loadClass("Countries");
		$countries		=	new Countries();
		$countries->loadWhere("1=1", "country");
		$this->assign("countries",$countries->toArray());
		
		$this->assign("org_id",$org_id);
		$this->assign("fishing_area_id",$fid);
		$this->setNoLayout();
		$this->render("dataFMAOrgFishingArea.tpl");	
	}
	
	public function dataFMAFishingAreaSaveAction(){
		$auth = BaseAuth::getInstance();
		
		$org_id			=	$this->getParam("org_id");
		$id				=	$this->getParam("fishing_area_id");
		$name			=	$this->getParam("fishing_area_name");
		$area			=	$this->getParam("area");
		$mac_certified	=	$this->getParam("mac_certified");
		$country_code	=	$this->getParam("country_code");
		
		Zend_loader::loadClass("FishingArea");
		$fishing_area	=	new FishingArea();
		
		if($auth){
		
			if($id)
				$fishing_area->load($id);
			$fishing_area->org_id				=	$org_id;
			$fishing_area->fishing_area_name	=	$name;
			$fishing_area->area					=	$area;
			$fishing_area->mac_certified		=	$mac_certified;
			$fishing_area->country_code			=	$country_code;
			
				if($id){
					$fishing_area->date_updated	=	date('Y-m-d H:i:s');
					$fishing_area->updated_by	=	$auth->id;
				}
				else{
					$fishing_area->date_created	=	date('Y-m-d H:i:s');
					$fishing_area->created_by	=	$auth->id;
				}
		}
		$fishing_area->save();
		$fishing_area->fishing_area_id		=	$fishing_area->id;
		$fishing_area->save();
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		$this->dataFMAEditRefreshAction();
	}
	
	public function dataFMAEditRefreshAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();		
		$this->assign("menuUtility" ,$menuUtility);
		
		//$id=$this->getParam("id");
		Zend_loader::loadClass("CartUtility");
		$cart 					=	CartUtility::getadminCart();
		$id						=	$cart->id;
		$org_maintenance_type	=	$cart->org_maintenance_type;
		
		$this->assign("org_id",$id);
		
		//organization types
		Zend_loader::loadClass("OrganizationTypes");
		$orgTypes=new OrganizationTypes();
		$orgTypes->loadWhere(" 1=1 ");
		$orgTypes->toArray();
		$this->assign("organizationTypes",$orgTypes->toArray());
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere(" 1=1 ",array("name"));
		$this->assign("currencies",$currencies->toArray());
		
		//ship_methods
		Zend_loader::loadClass("Shipmethods");
		$shipmethods=new Shipmethods();
		$shipmethods->loadWhere(" 1=1 ",array("name"));
		$this->assign("ship_methods",$shipmethods->toArray());
		
		if(!empty($id)){
			//organization
			Zend_loader::loadClass("Organizations");
			$orgs=new Organizations();
			$orgs->loadWhere(" org_id = ".$id);
			$orgs->toArray();
			$this->assign("organization",$orgs->toArray());
			
			//organization addresses
			Zend_loader::loadClass("OrganizationAddress");
			Zend_loader::loadClass("Address");
			$query = new QueryCreator();
			$query->addSelect(" oa.org_address_id,oa.org_id,oa.address_id,oa.address_type, oa.inactive, a.address1, a.city, a.province, a.country_code ");
			$query->addFrom(OrganizationAddress::_tablename." AS oa ");
			$query->addJoin(" LEFT JOIN ".Address::_tablename." AS a ON oa.address_id = a.address_id ");
			$query->addWhere(" oa.org_id =".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$org_addresses = $db->executeQuery($query->createSQL());
			$this->assign("org_addresses",$org_addresses);
			
			//organizattion contacts
			Zend_loader::loadClass("Contact");
			Zend_loader::loadClass("OrganizationContact");
			$query = new QueryCreator();
			$query->addSelect(" oc.org_id,oc.org_contact_id,oc.contact_id,oc.inactive,c.name,c.remarks,c.telephone_no,c.email ");
			$query->addFrom(OrganizationContact::_tablename." as oc ");
			$query->addJoin(" LEFT join ".Contact::_tablename." as c on c.contact_id=oc.contact_id ");
			$query->addWhere(" oc.org_id =".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$org_contacts = $db->executeQuery($query->createSQL());
			$this->assign("org_contacts",$org_contacts);
			
			//fishers
			Zend_loader::loadClass("Fisher");
			Zend_loader::loadClass("FishingArea");
			Zend_loader::loadClass("FishingAreas");
			Zend_loader::loadClass("Contact");
			Zend_loader::loadClass("Document");
			$query = new QueryCreator();
			$query->addSelect(" f.fisher_id,f.first_name,f.last_name,fa.fishing_area_name,d.link ");
			$query->addFrom(Fisher::_tablename." as f ");
			$query->addJoin(" LEFT JOIN ".FishingArea::_tablename." as fa on f.fishing_area_id=fa.fishing_area_id ");
			$query->addJoin(" LEFT JOIN ".Contact::_tablename." as c on f.contact_id=c.contact_id ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=c.photo_document_id ");
			$query->addWhere(" fa.org_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$fishers = $db->executeQuery($query->createSQL());
			$this->assign("fishers",$fishers);
			
			//fishing area
			$fishing_areas = new FishingAreas();
			$fishing_areas->loadWhere("org_id=".$id);
			$this->assign("fishing_areas",$fishing_areas->toArray());
		}
		
		$this->assign("org_maintenance_type",$org_maintenance_type);
		$this->setLayout("admin");
		$this->render("dataFMA_popup.tpl");
	}
	public function dataFMARefreshAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();		
		$this->assign("menuUtility" ,$menuUtility);

		/*$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(" projecte_entity.organizations as o ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries as c ON a.country_code=c.country_code ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		$this->setLayout("admin");
		$this->render("dataFMA.tpl");
	}
	public function dataFMASaveListChangesAction(){
		Zend_loader::loadClass("Organizations");
		$organizations=new Organizations();
		$organizations->loadWhere(" 1=1 ");
		
		Zend_loader::loadClass("Organization");
		$o=new Organization();
		foreach($organizations as $organization){
			if($this->getParam('del_'.$organization->org_id)){
				$o->clear();
				$o->load($organization->org_id);
				$o->delete(TRUE);
				continue;
			}
		}
		//$this->_redirect("/admin/dataFMARefresh");
		$this->dataFMARefreshAction();
	}
	public function dataFMASaveAListChangesAction(){
		
		$org_id=$this->getParam("org_id");
		$p_id=$this->getParam("primary_address");
		if($org_id){
			Zend_loader::loadClass("OrganizationAddresses");
			$organization_addresses=new OrganizationAddresses();
			$organization_addresses->loadWhere(" org_id=".$org_id);
			
			Zend_loader::loadClass("OrganizationAddress");
			$oa=new OrganizationAddress();
			foreach($organization_addresses as $org_add ){
				if($this->getParam('del_'.$org_add->org_address_id)){
					$oa->clear();
					$oa->load($org_add->org_address_id);
					$oa->delete(TRUE);
					continue;
				}
			}
			
			if($p_id){
				Zend_loader::loadClass("Organization");
				$org=new Organization();
				$org->load($org_id);
				$org->primary_address_id=$p_id;
				$org->save();
			}
		}
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	public function dataFMASaveCListChangesAction(){
		$org_id=$this->getParam("org_id");
		$p_id=$this->getParam("primary_contact");
		if($org_id){
			Zend_loader::loadClass("OrganizationContacts");
			$organization_contacts=new OrganizationContacts();
			$organization_contacts->loadWhere(" org_id=".$org_id);
			
			Zend_loader::loadClass("OrganizationContact");
			$oc=new OrganizationContact();
			foreach($organization_contacts as $org_con ){
				if($this->getParam('del_'.$org_con->org_contact_id)){
					$oc->clear();
					$oc->load($org_con->org_contact_id);
					$oc->delete(TRUE);
					continue;
				}
				
			}
			
			if($p_id){
				Zend_loader::loadClass("Organization");
				$org=new Organization();
				$org->load($org_id);
				$org->primary_contact_id=$p_id;
				$org->save();
			}
		}
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	public function dataFMASaveFListChangesAction(){
		$org_id=$this->getParam("org_id");
		if($org_id){
			Zend_loader::loadClass("Fisher");
			Zend_loader::loadClass("FishingArea");
			Zend_loader::loadClass("Contact");
			Zend_loader::loadClass("Document");
			$query = new QueryCreator();
			$query->addSelect(" f.id,f.fisher_id,f.first_name,f.last_name,fa.fishing_area_name,d.link ");
			$query->addFrom(Fisher::_tablename." as f ");
			$query->addJoin(" LEFT JOIN ".FishingArea::_tablename." as fa on f.fishing_area_id=fa.fishing_area_id ");
			$query->addJoin(" LEFT JOIN ".Contact::_tablename." as c on f.contact_id=c.contact_id ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=c.photo_document_id ");
			$query->addWhere(" fa.org_id=".$org_id);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$fishers = $db->executeQuery($query->createSQL());
			Zend_loader::loadClass("Fisher");
			$f=new Fisher();
			foreach($fishers as $fisher){
				if($this->getParam('del_'.$fisher['id'])){
					$f->clear();
					$f->load($fisher['fisher_id']);
					$f->delete(TRUE);
				}
			}
		}
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	
	public function dataFMASaveFAListChangesAction(){
		$params		=$this->getParam();
		$org_id		=$params['org_id'];
		
		Zend_loader::loadClass("FishingArea");
		
		foreach($params as $key=>$value){
			$tmp	=	strpos($key,'del_');
			if(gettype($tmp)=='integer'){
				$fishing_area=new FishingArea();
				$fishing_area->load(trim($key,'del_'));
				$fishing_area->delete(TRUE);
			}
		}
		
		//$this->_redirect("/admin/dataFMAEditRefresh/id/".$org_id);
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$org_id;
		
		$this->dataFMAEditRefreshAction();
	}
	
	public function dataFMASearchAction(){
		$names		=$this->getParam("names");
		$province	=$this->getParam("province");
		$country	=$this->getParam("country");
		
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Country");
		$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(Organization::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".Address::_tablename." as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN ".Country::_tablename." as c ON a.country_code=c.country_code ");
		$query->addWhere(" o.org_type='FMA' ");
		if($country)
			$query->addWhere(" c.country_code='".$country."' ");
		if($province)
			$query->addWhere(" a.province='".$province."' ");
		if($names)
			$query->addWhere(" o.name like '%".$names."%' OR o.trading_name like '%".$names."%' ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("dataFMA_table.tpl");
	}
	//FMA search
	public function dataFMAProvinceAction(){
		$country=$this->getParam("country");
		Zend_loader::loadClass("Address");
		if($country){
			$query=new QueryCreator();
			$query->addSelect(" Distinct a.province ");
			$query->addFrom(Address::_tablename." as a ");
			$query->addWhere(" a.country_code='".$country."'");
			
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$result = $db->executeQuery($query->createSQL());
			foreach($result as $row){
				if($row['province'])
					$option.='<option value="'.$row['province'].'" >'.$row['province'].'</option>';
			}
		}
		
		echo '
					<select name="province" id="province">
						<option value=""> --- </option>
						'.$option.'
					</select>';
	}
	//export to excel
	public function dataFMAExportAction(){
		//export to excel
		
		// typically this will be generated/read from a database table
		Zend_loader::loadClass("Organization");
		$query = new QueryCreator();
		$query->addSelect(" o.org_id,o.name,o.trading_name,o.primary_address_id,
							o.primary_contact_id,o.phone,o.fax,o.email,o.website,
							o.inactive,o.non_taxable,o.org_type,o.notes,o.credit_limit,
							o.ship_method,o.tax_id_number,o.bank_name,o.bank_account_number,
							o.currency_mark ");
		$query->addFrom(Organization::_tablename." AS o ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("Address");
		$query = new QueryCreator();
		$query->addSelect(" a.address_id,a.address1,a.city,a.province,a.country_code,a.zip ");
		$query->addFrom(Address::_tablename." as a ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result2 = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("Contact");
		$query = new QueryCreator();
		$query->addSelect(" c.contact_id,c.name,c.remarks,c.telephone_no,c.fax_no,
							c.email,c.birth_date,c.license_date,c.blood_group,c.height");
		$query->addFrom(Contact::_tablename." as c ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result3 = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("OrganizationType");
		$query = new QueryCreator();
		$query->addSelect(" org_type ");
		$query->addFrom(OrganizationType::_tablename);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result4 = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("Shipmethod");
		$query = new QueryCreator();
		$query->addSelect(" name ");
		$query->addFrom(Shipmethod::_tablename);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result5 = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("Currencie");
		$query = new QueryCreator();
		$query->addSelect(" name ");
		$query->addFrom(Currencie::_tablename);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result6 = $db->executeQuery($query->createSQL());
		
		$workbook = new Spreadsheet_Excel_Writer();
 
		/* Sending HTTP headers, this will popup a file save/open
		dialog in the browser when this file is run
		*/
		$workbook->send('Organizations'.time().'.xls');
		 
		// Create worksheets
		$worksheet1 =& $workbook->addWorksheet('Organizations');
		$worksheet2 =& $workbook->addWorksheet('Address');
		$worksheet3 =& $workbook->addWorksheet('Contacts');
		$worksheet4 =& $workbook->addWorksheet('Organization_Type');
		$worksheet5 =& $workbook->addWorksheet('Ship_Method');
		$worksheet6 =& $workbook->addWorksheet('Currencies');
				
		// Set header formating for Sheet 1
		$header =& $workbook->addFormat();
		$header->setBold();		// Make it bold
		$header->setColor('white');	// Make foreground color white
		$header->setFgColor("green");	// Set background color to green
		$header->setHAlign('center');	// Align text to center
		 
		// Write some data on Sheet 1
		$worksheet1->write(0, 0, 'Org ID', $header);
		$worksheet1->write(0, 1, 'Name', $header);
		$worksheet1->write(0, 2, 'Trading Name', $header);
		$worksheet1->write(0, 3, 'Primary Address ID', $header);
		$worksheet1->write(0, 4, 'Primary Contact ID', $header);
		$worksheet1->write(0, 5, 'Phone', $header);
		$worksheet1->write(0, 6, 'Fax', $header);
		$worksheet1->write(0, 7, 'Email', $header);
		$worksheet1->write(0, 8, 'Website', $header);
		$worksheet1->write(0, 9, 'Inactive', $header);
		$worksheet1->write(0, 10, 'Non Taxable', $header);
		$worksheet1->write(0, 11, 'Organization Type', $header);
		$worksheet1->write(0, 12, 'Notes', $header);
		$worksheet1->write(0, 13, 'Credit Limit', $header);
		$worksheet1->write(0, 14, 'Ship Method', $header);
		$worksheet1->write(0, 15, 'Tax ID Number', $header);
		$worksheet1->write(0, 16, 'Bank Name', $header);
		$worksheet1->write(0, 17, 'Bank Account', $header);
		$worksheet1->write(0, 18, 'Currency', $header);

		$y=1;
		foreach($result as $row){
			$x=0;
			foreach($row as $count){
				$worksheet1->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		

		//worksheet 2
		$worksheet2->write(0, 0, 'Address ID', $header);
		$worksheet2->write(0, 1, 'Street', $header);
		$worksheet2->write(0, 2, 'City', $header);
		$worksheet2->write(0, 3, 'Province', $header);
		$worksheet2->write(0, 4, 'Country Code', $header);
		$worksheet2->write(0, 5, 'Zip', $header);

		$y=1;
		foreach($result2 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet2->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 3
		$worksheet3->write(0, 0, 'Contact ID', $header);
		$worksheet3->write(0, 1, 'Name', $header);
		$worksheet3->write(0, 2, 'Remarks', $header);
		$worksheet3->write(0, 3, 'Telephone', $header);
		$worksheet3->write(0, 4, 'Fax', $header);
		$worksheet3->write(0, 5, 'Email', $header);
		$worksheet3->write(0, 6, 'Birthday', $header);
		$worksheet3->write(0, 7, 'License Date', $header);
		$worksheet3->write(0, 8, 'Blood Group', $header);
		$worksheet3->write(0, 9, 'Height', $header);

		$y=1;
		foreach($result3 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet3->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 4
		$worksheet4->write(0, 0, 'Organization Type', $header);

		$y=1;
		foreach($result4 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet4->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 5
		$worksheet5->write(0, 0, 'Ship Method', $header);

		$y=1;
		foreach($result5 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet5->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 6
		$worksheet6->write(0, 0, 'Currency', $header);

		$y=1;
		foreach($result6 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet6->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		// Send the file to the browser
		$workbook->close();
	}
	public function dataFMAImportAction(){
		if ( $_FILES['file']['tmp_name'] )
		{
			$auth = BaseAuth::getInstance();
			$data=new Spreadsheet_Excel_Reader();
			$data->setUTFEncoder('iconv');
			$data->setOutputEncoding('UTF-8');
			$data->read($_FILES['file']['tmp_name']);
			
			Zend_loader::loadClass("Organization");
			$org=new Organization();
			
			 for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
				$org->clear();
				$id							=$data->sheets[0]['cells'][$i][1];
				if(empty($id)){
					$org->date_created=date("Y-m-d H:i:s");
					$org->created_by = $auth->id;
				}
				else{
					$org->load($id);
					$org->date_updated=date("Y-m-d H:i:s");
					$org->updated_by = $auth->id;
				}
				$org->name						=$data->sheets[0]['cells'][$i][2];
				$org->trading_name				=$data->sheets[0]['cells'][$i][3];
				$org->primary_address_id		=$data->sheets[0]['cells'][$i][4];
				$org->primary_contact_id		=$data->sheets[0]['cells'][$i][5];
				$org->phone						=$data->sheets[0]['cells'][$i][6];
				$org->fax						=$data->sheets[0]['cells'][$i][7];
				$org->email						=$data->sheets[0]['cells'][$i][8];
				$org->website					=$data->sheets[0]['cells'][$i][9];
				$org->inactive					=$data->sheets[0]['cells'][$i][10];
				$org->non_taxable				=$data->sheets[0]['cells'][$i][11];
				$org->org_type					=$data->sheets[0]['cells'][$i][12];
				$org->notes						=$data->sheets[0]['cells'][$i][13];
				$org->credit_limit				=$data->sheets[0]['cells'][$i][14];
				$org->ship_method				=$data->sheets[0]['cells'][$i][15];
				$org->tax_id_number				=$data->sheets[0]['cells'][$i][16];
				$org->bank_name					=$data->sheets[0]['cells'][$i][17];
				$org->bank_account_number		=$data->sheets[0]['cells'][$i][18];
				$org->currency_mark				=$data->sheets[0]['cells'][$i][19];
				$org->save();
				
				$org->contact_id=$org->id;
				$org->save();
			}
		}
		//$this->_redirect("/admin/dataFMARefresh");
		$this->dataFMARefreshAction();
	}
	
	//Organization Maintenance
	public function dataOFMAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();		
		$this->assign("menuUtility" ,$menuUtility);

		/*$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(" projecte_entity.organizations as o ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries as c ON a.country_code=c.country_code ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("dataOFMA.tpl");	
	}
	public function dataOFMARefreshAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();		
		$this->assign("menuUtility" ,$menuUtility);

		/*$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(" projecte_entity.organizations as o ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries as c ON a.country_code=c.country_code ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		$this->setLayout("admin");
		$this->render("dataOFMA.tpl");
	}
	public function dataOFMASearchAction(){
		$names		=$this->getParam("names");
		$province	=$this->getParam("province");
		$country	=$this->getParam("country");
		
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Country");
		$query = new QueryCreator();
		$query->addSelect(" o.org_id as id,o.name,a.province,c.country,o.org_type ");
		$query->addFrom(Organization::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".Address::_tablename." as a ON o.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN ".Country::_tablename." as c ON a.country_code=c.country_code ");
		
		if($country)
			$query->addWhere(" c.country_code='".$country."' ");
		if($province)
			$query->addWhere(" a.province='".$province."' ");
		if($names)
			$query->addWhere(" o.name like '%".$names."%' OR o.trading_name like '%".$names."%' ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("dataFMA_table.tpl");
	}
	
	//TAC
	public function dataTMAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		/*$query = new QueryCreator();
		$query->addSelect(" t.tac_id, t.limit_quantity, t.date_start, t.date_expire, o.mac_certified,
							o.unsuitable_species, o.name, d.link, og.organism_group, fa.fishing_area_name,
							a.province,org.trading_name, c.country ");
		$query->addFrom(" projecte_inventory.tacs AS t ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organisms AS o ON o.organism_id = t.organism_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.documents AS d ON d.id = o.picture_document_id ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organism_groups AS og ON og.organism_group_id = o.organism_group_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.fishing_areas AS fa ON fa.fishing_area_id = t.fishing_area_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.organizations AS org ON org.org_id = fa.org_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON org.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries AS c ON c.country_code = fa.country_code ");
		//$query->addLimit(" 0,30 ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		$this->setNoLayout();
		$this->render("dataTAC.tpl");
	}
	public function dataTACEditAction(){
		
		//organisms
		Zend_loader::loadClass("Organisms");
		$orgs=new Organisms();
		$orgs->loadWhere("1 = 1",array("name"));
		$this->assign("organisms",$orgs->toArray());
		
		//fishing areas
		Zend_loader::loadClass("FishingAreas");
		$fas=new FishingAreas();
		$fas->loadWhere("1 = 1",array("fishing_area_id"));
		$this->assign("fishing_areas",$fas->toArray());
		
		$tac_id=$this->getParam("id");
		if(!empty($tac_id)){
			//Tac
			Zend_loader::loadClass("Tacs");
			$tac=new Tacs();
			$tac->loadWhere(" tac_id=".$tac_id,array("tac_id"));
			$this->assign("result",$tac->toArray());
		}
		
		$this->setNoLayout();
		$this->render("dataTAC_popup.tpl");
	}
	public function dataTACAddTacAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
		
		$id=$this->getParam("tac_id");
		
		Zend_loader::loadClass("Tac");
		$tac=new Tac();
		if(!empty($id)){
			$tac->load($id);
			$tac->date_updated	= date("Y-m-d H:i:s");
			$tac->updated_by	= $auth->username;
		}
		else{
			$tac->date_created 	= date("Y-m-d H:i:s");
			$tac->created_by	= $auth->username;
		}
		$tac->organism_id		=	$this->getParam("organisms");
		$tac->fishing_area_id	=	$this->getParam("fishing_areas");
		$tac->limit_quantity	=	$this->getParam("limit_quantity");
		$tac->date_start		=	$this->getParam("date_start");
		$tac->date_expire		=	$this->getParam("date_expire");
		$tac->current			=	$this->getParam("current");
		$tac->save();
		
		$tac->tac_id			=$tac->id;
		$tac->save();
		//$this->_redirect("/admin/dataTMRefresh");
		$this->dataTMRefreshAction();
	}
	public function dataTMSaveListChanges(){
		Zend_loader::loadClass("Tacs");
		Zend_loader::loadClass("Tac");
		$tacs=new Tacs();
		$tacs->loadWhere(" 1=1 ");
		$t=new Tac();
		foreach($tacs as $tac){
			$t->clear();
			if($this->getParam('del_'.$tac->tac_id)){
				$t->load($tac->tac_id);
				$t->delete(TRUE);
			}
		}
		
		//$this->_redirect("/admin/dataTMRefresh");
		$this->dataTMRefreshAction();
	}
	public function dataTMRefreshAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->setLayout("admin");
		$this->assign("menuUtility" ,$menuUtility);
		
		/*$query = new QueryCreator();
		$query->addSelect(" t.tac_id, t.limit_quantity, t.date_start, t.date_expire, o.mac_certified,
							o.unsuitable_species, o.name, d.link, og.organism_group, fa.fishing_area_name,
							a.province,org.trading_name, c.country ");
		$query->addFrom(" projecte_inventory.tacs AS t ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organisms AS o ON o.organism_id = t.organism_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.documents AS d ON d.id = o.picture_document_id ");
		$query->addJoin(" LEFT JOIN projecte_inventory.organism_groups AS og ON og.organism_group_id = o.organism_group_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.fishing_areas AS fa ON fa.fishing_area_id = t.fishing_area_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.organizations AS org ON org.org_id = fa.org_id ");
		$query->addJoin(" LEFT JOIN projecte_entity.addresses as a ON org.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN projecte_ref.countries AS c ON c.country_code = fa.country_code ");
		//$query->addLimit(" 0,30 ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);*/
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ", "country");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		$this->render("dataTAC.tpl");
	}
	public function dataTMSearchAction(){
		$fma=$this->getParam("fma");
		$group=$this->getParam("group");
		$names=$this->getParam("names");
		$country=$this->getParam("country");
		
		Zend_loader::loadClass("Tac");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("OrganismGroup");
		Zend_loader::loadClass("FishingArea");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Country");
		$query = new QueryCreator();
		$query->addSelect(" t.tac_id, t.limit_quantity, t.date_start, t.date_expire, o.mac_certified,
							o.unsuitable_species, o.name,o.scientific_name, d.link, og.organism_group, fa.fishing_area_name,
							a.province,org.trading_name, c.country ");
		$query->addFrom(Tac::_tablename." AS t ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." AS o ON o.organism_id = t.organism_id ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." AS d ON d.id = o.picture_document_id ");
		$query->addJoin(" LEFT JOIN ".OrganismGroup::_tablename." AS og ON og.organism_group_id = o.organism_group_id ");
		$query->addJoin(" LEFT JOIN ".FishingArea::_tablename." AS fa ON fa.fishing_area_id = t.fishing_area_id ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." AS org ON org.org_id = fa.org_id ");
		$query->addJoin(" LEFT JOIN ".Address::_tablename." as a ON org.primary_address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN ".Country::_tablename." AS c ON c.country_code = fa.country_code ");
		//$query->addLimit(" 0,30 ");
		
		if($fma)
			$query->addWhere(" fa.org_id=".$fma);
		if($country)
			$query->addWhere(" o.country_code='".$country."'");		
		if($group)
			$query->addWhere(" o.organism_group_id=".$group);
		if($names)
			$query->addWhere(" o.name like '%".$names."%' OR o.scientific_name like '%".$names."%' ");
			
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("dataTAC_table.tpl");
	}
	//export to excel
	public function dataTMExportAction(){
		//export to excel
		
		// typically this will be generated/read from a database table
		Zend_loader::loadClass("Tac");
		$query = new QueryCreator();
		$query->addSelect(" t.tac_id,t.organism_id,t.fishing_area_id,t.limit_quantity,t.date_start,
							t.date_expire,t.current");
		$query->addFrom(Tac::_tablename." AS t ");
		//$query->addLimit(" 0,30 ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("Organism");
		$query = new QueryCreator();
		$query->addSelect(" o.organism_id,o.name,o.scientific_name ");
		$query->addFrom(Organism::_tablename." as o ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result2 = $db->executeQuery($query->createSQL());
		
		Zend_loader::loadClass("FishingArea");
		Zend_loader::loadClass("Organization");
		$query = new QueryCreator();
		$query->addSelect(" fa.fishing_area_id,fa.fishing_area_name,o.trading_name,fa.area,fa.country_code  ");
		$query->addFrom(FishingArea::_tablename." as fa ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.org_id=fa.org_id ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result3 = $db->executeQuery($query->createSQL());
		
		$workbook = new Spreadsheet_Excel_Writer();
 
		/* Sending HTTP headers, this will popup a file save/open
		dialog in the browser when this file is run
		*/
		$workbook->send('Tacs'.time().'.xls');
		 
		// Create worksheets
		$worksheet1 =& $workbook->addWorksheet('Tacs');
		$worksheet2 =& $workbook->addWorksheet('Organisms');
		$worksheet3 =& $workbook->addWorksheet('Fishing_area');
				
		// Set header formating for Sheet 1
		$header =& $workbook->addFormat();
		$header->setBold();		// Make it bold
		$header->setColor('white');	// Make foreground color black
		$header->setFgColor("green");	// Set background color to green
		$header->setHAlign('center');	// Align text to center
		 
		// Write some data on Sheet 1
		$worksheet1->write(0, 0, 'Tac ID', $header);
		$worksheet1->write(0, 1, 'Organism ID', $header);
		$worksheet1->write(0, 2, 'Fishing Area ID', $header);
		$worksheet1->write(0, 3, 'Limit Quantity', $header);
		$worksheet1->write(0, 4, 'Date Start', $header);
		$worksheet1->write(0, 5, 'Date Expire', $header);
		$worksheet1->write(0, 6, 'Current', $header);
		
		$y=1;
		foreach($result as $row){
			$x=0;
			foreach($row as $count){
				$worksheet1->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		

		//worksheet 2
		$worksheet2->write(0, 0, 'Organism ID', $header);
		$worksheet2->write(0, 1, 'Name', $header);
		$worksheet2->write(0, 2, 'Scientific Name', $header);
		$y=1;
		foreach($result2 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet2->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		//worksheet 3
		$worksheet3->write(0, 0, 'Fishing Area ID', $header);
		$worksheet3->write(0, 1, 'Fishing Area Name', $header);
		$worksheet3->write(0, 2, 'Organization', $header);
		$worksheet3->write(0, 3, 'Area', $header);
		$worksheet3->write(0, 4, 'Country', $header);
		$y=1;
		foreach($result3 as $row){
			$x=0;
			foreach($row as $count){
				$worksheet3->write($y,$x,$count);
				$x++;
			}
			$y++;
		}
		
		// Send the file to the browser
		$workbook->close();
	}
	public function dataTMImportAction(){
		if ( $_FILES['file']['tmp_name'] )
		{
			$auth = BaseAuth::getInstance();
			$data=new Spreadsheet_Excel_Reader();
			$data->setUTFEncoder('iconv');
			$data->setOutputEncoding('UTF-8');
			$data->read($_FILES['file']['tmp_name']);
			
			Zend_loader::loadClass("Tac");
			 for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
				$tac=new Tac();
				$id							=$data->sheets[0]['cells'][$i][1];
				if(empty($id)){
					$tac->date_created=date("Y-m-d H:i:s");
					$tac->created_by = $auth->id;
				}
				else{
					$tac->load($id);
					$tac->date_updated=date("Y-m-d H:i:s");
					$tac->updated_by = $auth->id;
				}
				
				$tac->organism_id					=$data->sheets[0]['cells'][$i][2];
				$tac->fishing_area_id				=$data->sheets[0]['cells'][$i][3];
				$tac->limit_quantity				=$data->sheets[0]['cells'][$i][4];
				$tac->date_start					=$data->sheets[0]['cells'][$i][5];
				$tac->date_expire					=$data->sheets[0]['cells'][$i][6];
				$tac->current						=$data->sheets[0]['cells'][$i][7];
				$tac->save();
				
				$tac->tac_id = $tac->id;
				$tac->save();
			}
		}
		//$this->_redirect("/admin/dataTMRefresh");
		$this->dataTMRefreshAction();
	}
	
	public function systemAdminAction(){
		die("system admin");
	}
	
	//document library
	public function systemDLAction(){
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("DocumentLibrary");
		$query = new QueryCreator();
		$query->addSelect(" dl.*,d.link,d.title,d.description,d.date_created,d.created_by ");
		$query->addFrom(DocumentLibrary::_tablename." as dl ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=dl.id ");
		$query->addWhere(" dl.id>0 ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		for($i=0;$i<count($result);$i++){
			$filename = explode("/", $result[$i]['link']);
			$filename = array_reverse($filename ); // THIS WILL MAKE THE LAST ELEMENT THE FIRST
			$result[$i]['filename']=$filename[0]; 
		}
		$this->assign("results",$result);
		
		//for the MAP
		$query = new QueryCreator();
		$query->addSelect(" dl.*,d.link,d.title,d.description,d.date_created,d.created_by ");
		$query->addFrom(DocumentLibrary::_tablename." as dl ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=dl.id ");
		$query->addWhere(" dl.id=0 ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$map_result = $db->executeQuery($query->createSQL());
		$this->assign("map",$map_result[0]);
		
		$this->setNoLayout();
		$this->render("systemDocLib.tpl");	
	}
	public function systemDLEditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("Document");
			$doc=new Document();
			$doc->load($id);
			$this->assign("doc",$doc->toArray());
		}
		$this->setNoLayout();
		$this->render("systemDocLib_popup.tpl");
	}
	public function systemDLAddAction(){
		
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("DocumentLibrary");
		Zend_Loader::loadClass("AuthUtility");
		//Zend_Loader::loadClass("UploadFile");
		
		$auth = BaseAuth::getInstance();
  		$doc=new Document();
		$doclib=new DocumentLibrary();
		
		$id=$this->getParam("hid");
		if($id){
			$doc->load($id);
			$doclib->load($doc->id);
		}
		
        $pictureInput = $_FILES['document'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['document']['tmp_name'], $uploadDIR.'/'.$fileName);
			
			//$doc->link	=	$uploadFilename;
			$doc->link	=	$uploadDIR.'/'.$fileName;
		}
		
		$doc->title	=	$this->getParam("name");
		$doc->description = $this->getParam("name");
		$doc->date_created = date("Y-m-d H:i:s");
		$doc->created_by = $auth->username;
		$doc->save();
		
		$doc->document_id=$doc->id;
		$doc->save();
		
		$doclib->id=$doc->id;
		$doclib->document_id=$doc->id;
		$doclib->display_exporter=true;
		$doclib->display_importer=true;
		$doclib->display_government=true;
		$doclib->display_partners=true;
		$doclib->save();
		
		//$this->_redirect("/admin/systemDLrefresh");
		$this->systemDLRefreshAction();
	}
	public function systemDLrefreshAction(){
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("MenuUtility");
		
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		
		$this->setLayout("admin");
		$this->assign("menuUtility" ,$menuUtility);
		
		//SAME FUNCTION as SYSTEMDLACTION
		Zend_loader::loadClass("DocumentLibrary");
		Zend_loader::loadClass("Document");
		$query = new QueryCreator();
		$query->addSelect(" dl.*,d.link,d.title,d.description,d.date_created,d.created_by ");
		$query->addFrom(DocumentLibrary::_tablename." as dl ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=dl.id ");
		$query->addWhere(" dl.id>0 ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		for($i=0;$i<count($result);$i++){
			$filename = explode("/", $result[$i]['link']);
			$filename = array_reverse($filename ); // THIS WILL MAKE THE LAST ELEMENT THE FIRST
			$result[$i]['filename']=$filename[0]; 
		}
		$this->assign("results",$result);
		
		//for the MAP
		$query = new QueryCreator();
		$query->addSelect(" dl.*,d.link,d.title,d.description,d.date_created,d.created_by ");
		$query->addFrom(DocumentLibrary::_tablename." as dl ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on d.id=dl.id ");
		$query->addWhere(" dl.id=0 ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$map_result = $db->executeQuery($query->createSQL());
		$this->assign("map",$map_result[0]);
		
		$this->render("systemDocLib.tpl");	
	}
	public function systemDLSaveListChangesAction(){
		Zend_loader::loadClass("DocumentLibraries");
		Zend_loader::loadClass("Document");
		$dls=new DocumentLibraries();
		$dls->loadWhere(" 1=1 ");
		
		foreach($dls as $row){
			$record=$row->toArray();
			$id=$record['id'];
			
			$del='del_'.$id;
			$importer='cb_importer_'.$id;
			$exporter='cb_exporter_'.$id;
			$government='cb_government_'.$id;
			$partners='cb_partners_'.$id;
			
			$doc=new Document();
			$doc->load($id);
			$doclib=new DocumentLibrary();
			$doclib->load($id);
			
			//del
			if($this->getParam($del)){
				$doclib->delete(TRUE);
				$doc->delete(TRUE);
				continue;
			}
			
			//importer
			if($this->getParam($importer))
				$doclib->display_importer=1;
			else
				$doclib->display_importer=0;

			//exporter
			if($this->getParam($exporter))
				$doclib->display_exporter=1;
			else
				$doclib->display_exporter=0;
				
			//governent
			if($this->getParam($government))
				$doclib->display_government=1;
			else
				$doclib->display_government=0;
				
			//exporter
			if($this->getParam($partners))
				$doclib->display_partners=1;
			else
				$doclib->display_partners=0;
			
			$doclib->save();
		}
		
		//$this->_redirect("/admin/systemDLrefresh");
		$this->systemDLrefreshAction();
	}
	
	//user maintenance
	public function systemUMAction(){
	
  		Zend_Loader::loadClass("MenuUtility");
		
		$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//usergroups
		Zend_loader::loadClass("UserGroups");
		$groups=new UserGroups();
		$groups->loadWhere("1 = 1",array("name"));
		$this->assign("ugroups",$groups->toArray());
		
		//for the table
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("UserDetail");
		Zend_loader::loadClass("UserGroup");
		$query = new QueryCreator();
		$query->addSelect(" u.id,u.username,ud.first_name,ud.last_name,ud.middle_name,
							u2.username AS created_by,ud.date_enabled,ug.name as group_name ");
		$query->addFrom(User::_tablename." as u ");
		$query->addJoin(" LEFT JOIN ".UserDetail::_tablename." as ud 
						  ON ud.id=u.id ");
		$query->addJoin(" LEFT JOIN ".UserGroup::_tablename." as ug 
						  ON ug.user_group_code=u.user_group_code ");
						  
		$query->addJoin(" LEFT JOIN ".User::_tablename." AS u2 ON u2.id = ud.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->render("user_maintenance.tpl");
	}	
	public function systemUMFilterAction(){
		
		$group=$this->getParam("group");
		
		//for the table
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("UserDetail");
		Zend_loader::loadClass("UserGroup");
		$query = new QueryCreator();
		$query->addSelect(" u.id,u.username,ud.first_name,ud.last_name,ud.middle_name,
							u2.username AS created_by,ud.date_enabled,ug.name as group_name ");
		$query->addFrom(User::_tablename." as u ");
		$query->addJoin(" LEFT JOIN ".UserDetail::_tablename." as ud 
						  ON ud.id=u.id ");
		$query->addJoin(" LEFT JOIN ".UserGroup::_tablename." as ug 
						  ON ug.user_group_code=u.user_group_code ");
						  
		$query->addJoin(" LEFT JOIN ".User::_tablename." AS u2 ON u2.id = ud.created_by ");
		if($group)
			$query->addWhere(" u.user_group_code='".$group."' ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("user_maintenance_table.tpl");
	}
	public function systemSaveListChangesAction(){
		//users
		Zend_loader::loadClass("Users");
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("UserDetail");
		$users=new Users();
		$users->loadWhere("1 = 1");
		foreach($users as $user){
			if($this->getParam('del_'.$user->id)){
				$u=new User();
				$u->load($user->id);
				$u->delete(TRUE);
				
				$ud=new UserDetail();
				$ud->load($user->id);
				$ud->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemUMRefresh");
		$this->systemUMRefreshAction();
	}
	public function systemAddUserAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_loader::loadClass("UserDetail");
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Contact");
		Zend_loader::loadClass("Document");
		
		$user=new User();
		$detail = new UserDetail();
		$address=new Address();
		$contact=new Contact();
		$pic=new Document();
		$auth = BaseAuth::getInstance();
		
		$uid=$this->getParam("uid");
		
		if($uid)
		{
			$user->load($uid);
			$detail->load($uid);
			$address->load($detail->address_id);
			$contact->load($detail->contact_id);
			$pic->load($contact->photo_document_id);
		}
		else{
			$detail->created_by			=	$auth->id;
			$detail->date_enabled		=	date("Y-m-d H:i:s");
		}
		
		//user
		$user->username				=	$this->getParam("uname");
		if($this->getParam("npassword"))
			$user->password				=	md5($this->getParam("npassword"));
		$user->user_type_id			=	$this->getParam("utype");
		$user->assigned_country_code=	$this->getParam("country");
		$user->user_group_code		=	$this->getParam("ugroup");
		$user->mac_id				=	$this->getParam("macid");
		$user->org_id				=	$this->getParam("organization");
		$user->save();
		
		//address
		$address->address1=$this->getParam("street");
		$address->city=$this->getParam("city");
		$address->province=$this->getParam("province");
		$address->zip=$this->getParam("zip");
		$address->country_code=$this->getParam("country");
		$address->save();
		
		$pictureInput = $_FILES['fupic'];
        if (!empty($pictureInput['tmp_name'])) {
			Zend_Loader::loadClass("Zend_Session_Namespace");
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
            $uploadDIR = $client_session->CLIENT_DIR.$this->getConfig()->paths->upload;
            $uploadFileExt = strtolower(end(explode('.', $pictureInput['name'])));
            $fileName = strtolower(uniqid(rand())) .'.' . $uploadFileExt;
            copy($_FILES['fupic']['tmp_name'], $uploadDIR.'/'.$fileName);
			
			$pic->link	=	'/cad_clients/default/'.$this->getConfig()->paths->upload.'/'.$fileName;
			$pic->title	=	"Pic";
			$pic->save();
			
			$contact->photo_document_id	=	$pic->id;
		}
		
		//contact
		$contact->name				=	$this->getParam("fname").' '.$this->getParam("lname");
		$contact->telephone_no		=	$this->getParam("phone");
		$contact->fax_no			=	$this->getParam("fax");
		$contact->email				=	$this->getParam("email");
		$contact->birth_date		=	$this->getParam("bdate1");
		$contact->save();
		
		//user
		$detail->id					=	$user->id;
		$detail->first_name			=	$this->getParam("fname");
		$detail->middle_name		=	$this->getParam("mname");
		$detail->last_name			=	$this->getParam("lname");
		$detail->national_id		=	$this->getParam("nationalID");
		$detail->gender				=	$this->getParam("gender");
		$detail->address_id			=	$address->address_id;
		$detail->contact_id			=	$contact->contact_id;
		$detail->save();
		
		$detail->user_id			=	$detail->id;
		$detail->save();
		//$this->_redirect("/admin/systemUMRefresh");
		$this->systemUMRefreshAction();
	}
	public function systemEditUserAction(){
		$this->setNoLayout();
		
		//countries
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1", "country");
		$this->assign("countries",$countries->toArray());
		
		//usergroups
		Zend_loader::loadClass("UserGroups");
		$groups=new UserGroups();
		$groups->loadWhere("1 = 1",array("name"));
		$this->assign("ugroups",$groups->toArray());
		
		//types
		Zend_loader::loadClass("UserTypes");
		$types=new UserTypes();
		$types->loadWhere("1 = 1");
		$this->assign("types",$types->toArray());
		
		//organizations
		Zend_loader::loadClass("Organizations");
		$orgs=new Organizations();
		$orgs->loadWhere(" 1=1 ");
		$this->assign("orgs",$orgs->toArray());
		
		//for the table
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("UserType");
		Zend_loader::loadClass("UserGroup");
		Zend_loader::loadClass("UserDetail");
		Zend_loader::loadClass("Address");
		Zend_loader::loadClass("Contact");
		Zend_loader::loadClass("Document");
		$query = new QueryCreator();
		$query->addSelect(" u.id,u.org_id,u.username,ug.user_group_code,ut.id as ut_id,u.assigned_country_code as c_code,u.mac_id,
							ud.first_name,ud.middle_name,ud.last_name,ud.national_id,ud.created_by,ud.date_enabled,ud.gender,
							a.address1 as street,a.city,a.province,a.zip,c.telephone_no as phone,c.fax_no as fax,c.email,c.photo_document_id,c.birth_date as bdate,
							d.link ");
		$query->addFrom(User::_tablename." as u ");
		$query->addJoin(" LEFT JOIN ".UserGroup::_tablename." as ug on u.user_group_code=ug.user_group_code ");
		$query->addJoin(" LEFT JOIN ".UserType::_tablename." as ut on u.user_type_id=ut.id ");
		$query->addJoin(" LEFT JOIN ".UserDetail::_tablename." as ud ON ud.id=u.id ");
		$query->addJoin(" LEFT JOIN ".Address::_tablename." as a ON ud.address_id=a.address_id ");
		$query->addJoin(" LEFT JOIN ".Contact::_tablename." as c ON ud.contact_id=c.contact_id ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d ON c.photo_document_id=d.id ");
		$query->addWhere(" u.id=".$this->getParam("id"));

		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		
		//query
		$this->assign("email",	$result[0]['email']);
		$this->assign("fax",	$result[0]['fax']);
		$this->assign("phone",	$result[0]['phone']);
		$this->assign("zip",	$result[0]['zip']);
		$this->assign("province",$result[0]['province']);
		$this->assign("city",	$result[0]['city']);
		$this->assign("street",	$result[0]['street']);
		$this->assign("bdate",	$result[0]['bdate']);
		$this->assign("gender",	$result[0]['gender']);
		$this->assign("nationalID",$result[0]['national_id']);
		$this->assign("mname",	$result[0]['middle_name']);
		$this->assign("fname",	$result[0]['first_name']);
		$this->assign("lname",	$result[0]['last_name']);
		$this->assign("macid",	$result[0]['mac_id']);
		$this->assign("ut_id",	$result[0]['ut_id']);
		$this->assign("ug_code",$result[0]['user_group_code']);
		$this->assign("c_code",	$result[0]['c_code']);
		$this->assign("uname",	$result[0]['username']);
		$this->assign("uid",	$result[0]['id']);
		$this->assign("photo",	$result[0]['link']);
		$this->assign("uorg",	$result[0]['org_id']);
		$this->render('user_details_popup.tpl');
	}
	public function systemUMRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		$this->setLayout("admin");
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
	
		//usergroups
		Zend_loader::loadClass("UserGroups");
		$groups=new UserGroups();
		$groups->loadWhere("1 = 1",array("name"));
		$this->assign("ugroups",$groups->toArray());
				
		//for the table
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("UserDetail");
		Zend_loader::loadClass("UserGroup");
		$query = new QueryCreator();
		$query->addSelect(" u.id,u.username,ud.first_name,ud.last_name,ud.middle_name,
							u2.username AS created_by,ud.date_enabled,ug.name as group_name ");
		$query->addFrom(User::_tablename." as u ");
		$query->addJoin(" LEFT JOIN ".UserDetail::_tablename." as ud 
						  ON ud.id=u.id ");
		$query->addJoin(" LEFT JOIN ".UserGroup::_tablename." as ug 
						  ON ug.user_group_code=u.user_group_code ");
						  
		$query->addJoin(" LEFT JOIN ".User::_tablename." AS u2 ON u2.id = ud.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->render("user_maintenance.tpl");
	}
	
	public function mapAction(){
		Zend_Loader::loadClass("MenuUtility");
		
		$this->setNoLayout();
		$auth = BaseAuth::getInstance();
  		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
	
		$this->render("map.tpl");
	}
 
	//USER GROUPS
	public function systemUGAction(){
		//groups
		Zend_loader::loadClass("UserGroups");
		$groups=new UserGroups();
		#$groups->loadWhere("1 = 1");
		$this->assign("groups",$groups->getUserGroupsArray());
		
		$this->setNoLayout();
		$this->render("systemUG.tpl");
	}
	public function systemUGEditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("UserGroup");
			$ug=new UserGroup();
			$ug->load($id);
			$this->assign("ug",$ug->toArray());
		}
		$this->setNoLayout();
		$this->render("systemUG_popup.tpl");
	}
	public function systemUGSaveListChangesAction(){
		//usergroup
		Zend_loader::loadClass("UserGroups");
		Zend_loader::loadClass("UserGroup");
		
		$groups=new UserGroups();
		$groups->loadWhere("1 = 1");
		$groups->toArray();
		/* 
			author: Michael Ocana
			description : change $n to $ngroup cause error
			date : feb-07-2010
		*/		
		$ngroup = new UserGroup();
		foreach($groups as $group){
			if($this->getParam('del_'.$group->id)){
				$ngroup->clear();
				$ngroup->load($group->id);
				//print_r($u->toArray());die();
				$ngroup->delete(TRUE);
			}
		}
		//$this->systemUGAction();
		$this->systemUGRefreshAction();
	}
	public function systemUGAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//usergroup
		Zend_loader::loadClass("UserGroup");
		$group = new UserGroup();
		
		if($this->getParam("hid")){
			$group->load($this->getParam("hid"));
		}
		/* 
			author: Michael Ocana
			description : don't update usergroup data if put in else
			date : feb-07-2010
		*/		
		//else{
			$group->user_group_code	= $this->getParam("gcode");
			$group->created_by		= $auth->id;
			$group->date_created	= date("Y-m-d H:i:s");
		//}
		$group->name = $this->getParam("name");
		$group->save();
		//$this->_redirect("/admin/systemUGRefresh");	
		$this->systemUGRefreshAction();
	}
	public function systemUGRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//groups
		Zend_loader::loadClass("UserGroups");
		$groups=new UserGroups();
		$groups->loadWhere("1 = 1");
		$this->assign("groups",$groups->toArray());
		
		$this->setLayout("admin");
		$this->render("systemUG.tpl");
	}
 
	//REJECT REASONS
	public function systemRRAction(){
		//reject reasons
		Zend_loader::loadClass("RejectReason");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" r.id,r.reason,r.date_created,u.username as created_by ");
		$query->addFROM(RejectReason::_tablename." as r ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u on u.id=r.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("reasons",$result);
		
		$this->setNoLayout();
		$this->render("systemRR.tpl");
	}
	public function systemRREditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("RejectReason");
			$reason=new RejectReason();
			$reason->load($id);
			$this->assign("reason",$reason->toArray());
		}
		$this->setNoLayout();
		$this->render("systemRR_popup.tpl");
	}
	public function systemRRSaveListChangesAction(){
		//usergroup
		Zend_loader::loadClass("RejectReasons");
		Zend_loader::loadClass("RejectReason");
		
		$reasons=new RejectReasons();
		$reasons->loadWhere("1 = 1");
		$reasons->toArray();
		foreach($reasons as $reason){
			if($this->getParam('del_'.$reason->id)){
				$r=new RejectReason();
				$r->load($reason->id);
				$r->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemRRRefresh");
		$this->systemRRRefreshAction();
	}
	public function systemRRAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//reason
		Zend_loader::loadClass("RejectReason");
		$reason					=new RejectReason();
		
		if($this->getParam("hid")){
			$reason->load($this->getParam("hid"));
			$reason->updated_by		=$auth->id;
			$reason->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$reason->created_by		=$auth->id;
			$reason->date_created	=date("Y-m-d H:i:s");
		}
		$reason->reason			=$this->getParam("reason");
		
		$reason->save();
		$reason->reject_reason_id=$reason->id;
		$reason->save();
		//$this->_redirect("/admin/systemRRRefresh");	
		$this->systemRRRefreshAction();
	}
	public function systemRRRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//reject reasons
		Zend_loader::loadClass("RejectReason");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" r.id,r.reason,r.date_created,u.username as created_by ");
		$query->addFROM(RejectReason::_tablename." as r ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u on u.id=r.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("reasons",$result);
		
		$this->setLayout("admin");
		$this->render("systemRR.tpl");
	}
 
	//SKILL AREAS
	public function systemSAAction(){
		//skill
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("Skill");
		$query=new QueryCreator();
		$query->addSelect("  s.id,s.skill,s.date_created,u.username as created_by ");
		$query->addFrom(Skill::_tablename." as s ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=s.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("skills",$result);
		
		$this->setNoLayout();
		$this->render("systemSA.tpl");
	}
	public function systemSAEditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("Skill");
			$s=new Skill();
			$s->load($id);
			$this->assign("skill",$s->toArray());
		}
		$this->setNoLayout();
		$this->render("systemSA_popup.tpl");
	}
	public function systemSASaveListChangesAction(){
		//skills
		Zend_loader::loadClass("Skills");
		Zend_loader::loadClass("Skill");
		
		$skills=new Skills();
		$skills->loadWhere("1 = 1");
		$skills->toArray();
		foreach($skills as $skill){
			if($this->getParam('del_'.$skill->id)){
				$s=new Skill();
				$s->load($skill->id);
				//$s->loadWhere(" skill_id=".$skill->skill_id);
				$s->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemSARefresh");
		$this->systemSARefreshAction();
	}
	public function systemSAAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//skill
		Zend_loader::loadClass("Skill");
		$skill					=new Skill();
		
		if($this->getParam("hid")){
			$skill->load($this->getParam("hid"));
			$skill->updated_by		=$auth->id;
			$skill->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$skill->created_by		=$auth->id;
			$skill->date_created	=date("Y-m-d H:i:s");
		}
		$skill->skill			=$this->getParam("skill");
		$skill->save();
		$skill->skill_id		=$skill->id;
		$skill->save();
		//$this->_redirect("/admin/systemSARefresh");	
		$this->systemSARefreshAction();
	}
	public function systemSARefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//skill
		Zend_loader::loadClass("Skill");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect("  s.id,s.skill,s.date_created,u.username as created_by ");
		$query->addFrom(Skill::_tablename." as s ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=s.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("skills",$result);
		
		$this->setLayout("admin");
		$this->render("systemSA.tpl");
	}
 
	//CURRENCY
	public function systemCAction(){
		//currency
		/*Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");*/
		Zend_loader::loadClass("Currencie");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect("  c.id,c.name,c.currency_mark,c.description,c.date_created,u.username as created_by ");
		$query->addFrom(Currencie::_tablename." as c ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=c.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("currencies",$result);
		
		$this->setNoLayout();
		$this->render("systemC.tpl");
	}
	public function systemCEditAction(){
		$id=$this->getParam("id");
		if($id){
			//currency
			Zend_loader::loadClass("Currencie");
			$c=new Currencie();
			$c->load($id);
			$this->assign("currency",$c->toArray());
		}
		$this->setNoLayout();
		$this->render("systemC_popup.tpl");
	}
	public function systemCSaveListChangesAction(){
		//currencies
		Zend_loader::loadClass("Currencies");
		Zend_loader::loadClass("Currencie");
		
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");
		$currencies->toArray();
		$c=new Currencie();
		foreach($currencies as $currency){
			if($this->getParam('del_'.$currency->id)){
				$c->clear();
				$c->load($currency->id);
				$c->delete(TRUE);
				//$sql=" delete from projecte_ref.currencies where name='".$currency->name."'";
				//$db = DBCon::instance();
				//$result = $db->executeQuery($sql);
			}
		}
		//$this->_redirect("/admin/systemCRefresh");
		$this->systemCRefreshAction();
	}
	public function systemCAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//currencie
		Zend_loader::loadClass("Currencie");
		$currency					=new Currencie();
		
		if($this->getParam("hid")){
			$currency->load($this->getParam("hid"));
			$currency->updated_by		=$auth->id;
			$currency->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$currency->created_by		=$auth->id;
			$currency->date_created	=date("Y-m-d H:i:s");
		}
		$currency->name			=$this->getParam("name");
		$currency->currency_mark=$this->getParam("mark");
		$currency->description	=$this->getParam("description");
		
		$currency->save();
		//$this->_redirect("/admin/systemCRefresh");	
		$this->systemCRefreshAction();
	}
	public function systemCRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//currency
		Zend_loader::loadClass("Currencie");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect("  c.id,c.name,c.currency_mark,c.description,c.date_created,u.username as created_by ");
		$query->addFrom(Currencie::_tablename." as c ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=c.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("currencies",$result);
		
		$this->setLayout("admin");
		$this->render("systemC.tpl");
	}
 
	//COUNTRY
	public function systemC1Action(){
		//countries
		/*Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1");
		$this->assign("countries",$countries->toArray());*/
		Zend_loader::loadClass("Country");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect("  c.id,c.country_code,c.country,c.region,c.currency_mark,
							 c.currency_name,c.date_created,u.username as created_by ");
		$query->addFrom(Country::_tablename." as c ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=c.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("countries",$result);
		
		$this->setNoLayout();
		$this->render("systemC1.tpl");
	}
	public function systemC1EditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("Country");
			$country=new Country();
			$country->load($id);
			//$country->loadWhere(" country_code='".$id."'");
			$this->assign("country",$country->toArray());
		}
		
		//currency
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");
		$this->assign("currencies",$currencies->toArray());
		
		$this->setNoLayout();
		$this->render("systemC1_popup.tpl");
	}
	public function systemC1SaveListChangesAction(){
		//countries
		Zend_loader::loadClass("Countries");
		Zend_loader::loadClass("Country");
		
		$countries=new Countries();
		$countries->loadWhere("1 = 1");
		$countries->toArray();
		foreach($countries as $country){
			if($this->getParam('del_'.$country->id)){
				$c=new Country();
				$c->load($country->id);
				$c->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemC1Refresh");
		$this->systemC1RefreshAction();
	}
	public function systemC1AddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//currencie
		Zend_loader::loadClass("Currencie");
		$currency					=new Currencie();
		
		//country
		Zend_loader::loadClass("Country");
		$country					=new Country();
		
		if($this->getParam("hid")){
			
			$country->load($this->getParam("hid"));
			//$country->loadWhere(" country_code='".$this->getParam("hcode")."' ");
			$country->updated_by		=$auth->id;
			$country->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$country->created_by		=$auth->id;
			$country->date_created	=date("Y-m-d H:i:s");
		}
		
		$currency->loadWhere(" name='".$this->getParam("mark")."' ");
		
		$country->country_code	=$this->getParam("code");
		$country->country		=$this->getParam("country");
		$country->region		=$this->getParam("region");
		$country->currency_mark	=$currency->currency_mark;
		$country->currency_name	=$currency->name;
		
		$country->save();
		//$this->_redirect("/admin/systemC1Refresh");	
		$this->systemCRefreshAction();
	}
	public function systemC1RefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//countries
		/*Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere("1 = 1");
		$this->assign("countries",$countries->toArray());*/
		Zend_loader::loadClass("Country");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect("  c.id,c.country_code,c.country,c.region,c.currency_mark,
							 c.currency_name,c.date_created,u.username as created_by ");
		$query->addFrom(Country::_tablename." as c ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=c.created_by ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("countries",$result);
		
		$this->setLayout("admin");
		$this->render("systemC1.tpl");
	}
 
	//SHIP METHODS
	public function systemMTAction(){
		//ship methods
		Zend_loader::loadClass("Shipmethod");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" sm.id,sm.name, sm.description, sm.freight_org_id, sm.taxable,
							sm.flat_amount, sm.currency_mark, sm.date_created,
							u.username as created_by, o.trading_name  ");
		$query->addFrom(Shipmethod::_tablename." as sm ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o ON sm.freight_org_id=o.org_id ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON sm.created_by=u.id ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("shipmethods",$result);
		
		$this->setNoLayout();
		$this->render("systemSM.tpl");
	}
	public function systemMTEditAction(){
		$id=$this->getParam("id");
		if($id){
			//shipmethod
			Zend_loader::loadClass("Shipmethod");
			$sm=new Shipmethod();
			$sm->load($id);
			$this->assign("sm",$sm->toArray());
		}
		
		//currency
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");
		$this->assign("currencies",$currencies->toArray());
		
		//organizations
		Zend_loader::loadClass("Organization");
		$query=new QueryCreator();
		$query->addSelect(" o.org_id,o.trading_name ");
		$query->addFrom(Organization::_tablename." as o ");
		$query->addWhere(" o.org_type='Freight '");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("orgs",$result);
		
		$this->setNoLayout();
		$this->render("systemSM_popup.tpl");
	}
	public function systemMTSaveListChangesAction(){
		//ship methods
		Zend_loader::loadClass("Shipmethods");
		Zend_loader::loadClass("Shipmethod");
		
		$shipmethods=new Shipmethods();
		$shipmethods->loadWhere("1 = 1");
		$shipmethods->toArray();
		foreach($shipmethods as $shipmethod){
			if($this->getParam('del_'.$shipmethod->id)){
				$sm=new Shipmethod();
				$sm->load($shipmethod->id);
				$sm->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemMTRefresh");
		$this->systemMTRefreshAction();
	}
	public function systemMTAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//shipmethod
		Zend_loader::loadClass("Shipmethod");
		$shipmethod					=new Shipmethod();
		
		if($this->getParam("hid")){
			$shipmethod->load($this->getParam("hid"));
			$shipmethod->updated_by		=$auth->id;
			$shipmethod->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$shipmethod->created_by		=$auth->id;
			$shipmethod->date_created	=date("Y-m-d H:i:s");
		}
		$shipmethod->name			=$this->getParam("name");
		$shipmethod->freight_org_id =$this->getParam("org_id");
		$shipmethod->taxable		=$this->getParam("taxable");
		$shipmethod->flat_amount	=$this->getParam("flat_amount");
		$shipmethod->currency_mark	=$this->getParam("mark");
		$shipmethod->description	=$this->getParam("description");
		
		$shipmethod->save();
		$shipmethod->ship_method_id=$shipmethod->id;
		$shipmethod->save();
		//$this->_redirect("/admin/systemMTRefresh");
		$this->systemMTRefreshAction();
	}
	public function systemMTRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//ship methods
		Zend_loader::loadClass("Shipmethod");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" sm.id,sm.name, sm.description, sm.freight_org_id, sm.taxable,
							sm.flat_amount, sm.currency_mark, sm.date_created,
							u.username as created_by, o.trading_name  ");
		$query->addFrom(Shipmethod::_tablename." as sm ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o ON sm.freight_org_id=o.org_id ");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON sm.created_by=u.id ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("shipmethods",$result);
		
		//organizations
		$query=new QueryCreator();
		$query->addSelect(" o.org_id,o.trading_name ");
		$query->addFrom(Organization::_tablename." as o ");
		$query->addWhere(" o.org_type='Freight '");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("orgs",$result);
		
		$this->setLayout("admin");
		$this->render("systemSM.tpl");
	}
 
	//TRAININGS
	public function systemTAction(){
		//training
		Zend_loader::loadClass("Training");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Skill");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" t.id,t.org_id,t.skill_id,t.course_title,t.trainor,
							t.date_training,t.date_created,u.username as created_by,o.trading_name,s.skill ");
		$query->addFrom(Training::_tablename." as t ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o ON o.org_id=t.org_id");
		$query->addJoin(" LEFT JOIN ".Skill::_tablename." as s ON s.id=t.skill_id");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=t.created_by ");
		$query->addWhere(" 1=1 Order by t.date_created ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("trainings",$result);
		
		$this->setNoLayout();
		$this->render("systemT.tpl");
	}
	public function systemTEditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("Training");
			$training=new Training();
			$training->load($id);
			$this->assign("training",$training->toArray());
		}
		
		//skills
		Zend_loader::loadClass("Skills");
		$skills=new Skills();
		$skills->loadWhere("1 = 1");
		$this->assign("skills",$skills->toArray());
		
		//orgs
		Zend_loader::loadClass("Organizations");
		$orgs=new Organizations();
		$orgs->loadWhere("1 = 1");
		$this->assign("organizations",$orgs->toArray());
		
		$this->setNoLayout();
		$this->render("systemT_popup.tpl");
	}
	public function systemTSaveListChangesAction(){
		//trainings
		Zend_loader::loadClass("Trainings");
		Zend_loader::loadClass("Training");
		
		$trainings=new Trainings();
		$trainings->loadWhere("1 = 1");
		$trainings->toArray();
		foreach($trainings as $training){
			if($this->getParam('del_'.$training->id)){
				$t=new Training();
				$t->load($training->id);
				$t->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemTRefresh");
		$this->systemTRefreshAction();
	}
	public function systemTAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//training
		Zend_loader::loadClass("Training");
		$training					=new Training();
		
		if($this->getParam("hid")){
			$training->load($this->getParam("hid"));
			$training->updated_by		=$auth->id;
			$training->date_updated	=date("Y-m-d H:i:s");
		}
		else{
			$training->created_by		=$auth->id;
			$training->date_created	=date("Y-m-d H:i:s");
		}
		
		$training->course_title			=$this->getParam("course_title");
		$training->trainor 				=$this->getParam("trainor");
		$training->date_training		=$this->getParam("date_training");
		$training->org_id				=$this->getParam("org");
		$training->skill_id				=$this->getParam("skill");
		
		$training->save();
		$training->training_id=$training->id;
		$training->save();
		//$this->_redirect("/admin/systemTRefresh");
		$this->systemTRefreshAction();
	}
	public function systemTRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//training
		Zend_loader::loadClass("Training");
		Zend_loader::loadClass("Skill");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("User");
		$query=new QueryCreator();
		$query->addSelect(" t.id,t.org_id,t.skill_id,t.course_title,t.trainor,
							t.date_training,t.date_created,u.username as created_by,o.trading_name,s.skill ");
		$query->addFrom(Training::_tablename." as t ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as o ON o.org_id=t.org_id");
		$query->addJoin(" LEFT JOIN ".Skill::_tablename." as s ON s.id=t.skill_id");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u ON u.id=t.created_by ");
		$query->addWhere(" 1=1 Order by t.date_created ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("trainings",$result);
		
		$this->setLayout("admin");
		$this->render("systemT.tpl");
	}
 
	//PRICE MANAGEMENT
	public function systemPMAction(){
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		$this->setNoLayout();
		$this->render("systemPM.tpl");
	}
	public function systemPMEditAction(){
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");
		$this->assign("currencies",$currencies->toArray());
		
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("Organism");
			Zend_loader::loadClass("Document");
			Zend_loader::loadClass("OrganizationOrganism");
			Zend_loader::loadClass("Organization");
			Zend_loader::loadClass("OrganismAttribute");
			
			$query=new QueryCreator();
			$query->addSelect(" o.organism_id,o.moid,o.name,o.sale_unit,o.default_inventory_cost,
								o.default_base_price_sale,o.currency_mark,d.link,o.created_by,o.date_created ");
			$query->addFrom(Organism::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on o.picture_document_id=d.id");
			$query->addWhere(" o.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("organism",$result);
			
			//organizations_organisms
			$query	=	new QueryCreator();
			$query->addSelect("oo.org_organism_id,oo.base_price_sale,oa.*,o.name as organization_name");
			$query->addFrom(OrganizationOrganism::_tablename." as oo ");
			$query->addJoin("LEFT JOIN ".OrganismAttribute::_tablename." as oa ON oa.organism_attribute_id=oo.organism_attribute_id");
			$query->addJoin("LEFT JOIN ".Organization::_tablename." as o ON oo.org_id=o.org_id");
			$query->addWhere("oo.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("organization_organisms",$result);
		}
		$this->setNoLayout();
		$this->render("systemPM_edit.tpl");
	}
	public function systemPMSaveListChangesAction(){
		//organisms
		Zend_loader::loadClass("Organisms");
		Zend_loader::loadClass("Organism");
		
		$orgs=new Organisms();
		$orgs->loadWhere("1 = 1");
		$orgs->toArray();
		foreach($orgs as $org){
			if($this->getParam('del_'.$org->organism_id)){
				$o=new Organism();
				$o->load($org->organism_id);
				$o->delete(TRUE);
			}
		}
		//$this->_redirect("/admin/systemPMRefresh");
		$this->systemPMRefreshAction();
	}
	public function systemPMAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
  		
		//organism
		Zend_loader::loadClass("Organism");
		$org					=new Organism();
		
		if($this->getParam("hid")){
			$org->load($this->getParam("hid"));
			$org->updated_by			=$auth->id;
			$org->date_updated			=date("Y-m-d H:i:s");
			$org->sale_unit				=$this->getParam("sale_unit");
			$org->default_inventory_cost=$this->getParam("default_inventory_cost");
			$org->default_base_price_sale=$this->getParam("default_base_price_sale");
			$org->currency_mark			=$this->getParam("currency_mark");
			
			$org->save();
		}
		//$this->_redirect("/admin/systemPMRefresh");
		$this->systemPMRefreshAction();
	}
	public function systemPMRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		Zend_loader::loadClass("OrganismGroups");
		$groups=new OrganismGroups();
		$groups->loadWhere(" 1=1 ");
		$this->assign("groups",$groups->toArray());
		
		$this->setLayout("admin");
		$this->render("systemPM.tpl");
	}
	public function systemPMSearchAction(){
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("OrganismGroup");
		Zend_loader::loadClass("OrganizationOrganism");
		
		$fma=$this->getParam("fma");
		$group=$this->getParam("group");
		$names=$this->getParam("names");
		
		
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("User");
		Zend_loader::loadClass("OrganizationOrganism");
		$query=new QueryCreator();
		$query->addSelect(" distinct o.organism_id,o.moid,o.name,o.sale_unit,o.default_inventory_cost,
							o.default_base_price_sale,o.currency_mark,d.link,u.username as created_by,o.date_created ");
		$query->addFrom(Organism::_tablename." as o ");
		$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on o.picture_document_id=d.id");
		$query->addJoin(" LEFT JOIN ".User::_tablename." as u on u.id=o.created_by ");
		//$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.organism_id=o.organism_id ");

		if($fma){
			$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo ON oo.organism_id=o.organism_id ");
			$query->addWhere(" oo.org_id=".$fma);
		}
		if($group)
			$query->addWhere(" o.organism_group_id=".$group);
		if($names)
			$query->addWhere(" o.name like '%".$names."%' OR o.scientific_name like '%".$names."%' ");
		
		//$query->addLimit(" 0,10 ");
		$db = DBCon::instance();
		//echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("organisms",$result);
		
			
		$this->setNoLayout();
		$this->render("systemPM_table.tpl");
	}
	public function systemPMEditOrgOrganismAction(){
	
		$org_organism_id	=		$this->getParam("org_organism_id");
		$organism_id		=		$this->getParam("organism_id");
		
		//organization_organism //item
		if($org_organism_id){
			Zend_loader::loadClass("OrganizationOrganism");
			$result			=		new OrganizationOrganism();
			$result->load($org_organism_id);
			$item			=		$result->toArray();
			$this->assign("item",$item);
			
			if(!empty($item->organism_attribute_id)){
				Zend_loader::loadClass("OrganismAttribute");
				$attribute		=		new OrganismAttribute();
				$attribute->load($item['organism_attribute_id']);
				$this->assign("attribute",$attribute->toArray());
				//print_r($attribute->toArray());
			}
			//print_r($item);
		}
		
		//organism
		Zend_loader::loadClass("Organism");
		$organism			=		new Organism();
		$organism->load($organism_id);
		$this->assign("organism",$organism->toArray());
		
		//ordering status
		$ordering_status	=		array('Active','Inactive');
		$this->assign("ordering_status",$ordering_status);
		
		//default supplier
		Zend_loader::loadClass("Organizations");
		$organizations		=		new Organizations();
		$organizations->loadWhere("1=1");
		$this->assign("organizations",$organizations->toArray());
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies			=		new Currencies();
		$currencies->loadWhere("1=1");
		$this->assign("currencies",$currencies->toArray());
		
		//sizes
		Zend_loader::loadClass("Sizes");
		$sizes				=		new Sizes();
		$sizes->loadWhere("1=1");
		$this->assign("sizes",$sizes->toArray());
		
		$this->setNoLayout();
		$this->render("systemPM_popup.tpl");
		
	}
	public function systemPMSaveOrgOrganismAction(){
		//print_r($this->getParam());die();
		$auth = BaseAuth::getInstance();
		Zend_loader::loadClass("OrganizationOrganism");
		$oo_temp	=	new OrganizationOrganism();
		
		if($auth){
			$organism_id			=		$this->getParam("organism_id");
			$org_organism_id		=		$this->getParam("org_organism_id");
			$oo_temp->load($org_organism_id);
			$organism_attribute_id	=		$oo_temp->organism_attribute_id;
			$org_id					=		$this->getParam("organization");
			
			Zend_loader::loadClass("OrganismAttribute");
			$organism_attribute								=	new OrganismAttribute();
	
			if(!empty($organism_attribute_id)){
				//$organism_attribute->id						=	$organism_attribute_id;
				//$organism_attribute->organism_attribute_id	=	$organism_attribute_id;
				$organism_attribute->load($organism_attribute_id);
				$organism_attribute->updated_by				=	$auth->id;
				$organism_attribute->date_updated			=	date('Y-m-d H:i:s');
			}
			else{
				$organism_attribute->created_by				=	$auth->id;
				$organism_attribute->date_created			=	date('Y-m-d H:i:s');
			}
			
			$organism_attribute->size_code					=	$this->getParam("size_code");	
			$organism_attribute->estimated_length			=	$this->getParam("estimated_length");
			$organism_attribute->estimated_weight			=	$this->getParam("estimated_weight");
			$organism_attribute->organism_id				=	$organism_id;
			
			$organism_attribute->save();
			$organism_attribute->organism_attribute_id		=	$organism_attribute->id;
			$organism_attribute->save();
			
			//organization organism
			$organization_organism	=	new OrganizationOrganism();
			
			if(!empty($org_organism_id)){
				$organization_organism->org_organism_id		=	$org_organism_id;
				$organization_organism->id					=	$org_organism_id;
				$organization_organism->load($org_organism_id);
				$organization_organism->date_updated		=	date('Y-m-d H:i:s');
				$organization_organism->updated_by			=	$auth->id;
			}
			else{
				$organization_organism->date_created		=	date('Y-m-d H:i:s');
				$organization_organism->created_by			=	$auth->id;
			}
			
			$organization_organism->org_id					=	$org_id;
			$organization_organism->organism_id				=	$organism_id;
			$organization_organism->organism_attribute_id	=	$organism_attribute->organism_attribute_id;
			$organization_organism->ordering_status			=	$this->getParam("ordering_status");
			$organization_organism->inventory_cost			=	$this->getParam("inventory_cost");
			$organization_organism->last_buy				=	$this->getParam("last_buy");
			$organization_organism->last_sale				=	$this->getParam("last_sale");
			$organization_organism->estimated_buy			=	$this->getParam("estimated_buy");
			$organization_organism->non_taxable				=	$this->getParam("non_taxable");
			$organization_organism->base_price_sale			=	$this->getParam("base_price_sale");
			$organization_organism->reorder_level			=	$this->getParam("reorder_level");
			$organization_organism->min_order				=	$this->getParam("min_order");
			$organization_organism->lead_days				=	$this->getParam("lead_days");
			$organization_organism->notes					=	$this->getParam("notes");
			$organization_organism->default_supplier_id		=	$this->getParam("default_supplier");
			$organization_organism->order_on_hold			=	$this->getParam("order_on_hold");
			$organization_organism->currency_mark			=	$this->getParam("currency_mark");
			
			$unsuitable	= $this->getParam("unsuitable");
			if(!empty($unsuitable))
				$organization_organism->unsuitable			=	$unsuitable;
			else	
				$organization_organism->unsuitable			=	0;
				
			$organization_organism->save();
			$organization_organism->org_organism_id			=	$organization_organism->id;
			$organization_organism->save();	
		}
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$organism_id;
		$this->systemPMEditRefreshAction();
	}
	
	public function systemPMOOListChangesAction(){
		//print_r($this->getParam());die();
		Zend_loader::loadClass("OrganizationOrganism");
		$params						=		$this->getParam();
		$organism_id				=		$params['hid'];
		
		foreach($params as $key=>$value){
			if(is_numeric(strpos($key,'del_'))){
				$organization_organism	=	new OrganizationOrganism();
				$organization_organism->load(trim($key,'del_'));
				$organization_organism->delete(TRUE);
			}
		}
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$cart->id=$organism_id;
		$this->systemPMEditRefreshAction();
	}
	
	public function systemPMEditRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//currencies
		Zend_loader::loadClass("Currencies");
		$currencies=new Currencies();
		$currencies->loadWhere("1 = 1");
		$this->assign("currencies",$currencies->toArray());
		
		Zend_loader::loadClass("CartUtility");
		$cart = CartUtility::getadminCart();
		$id=$cart->id;
		
		if($id){
			Zend_loader::loadClass("Organism");
			Zend_loader::loadClass("Document");
			Zend_loader::loadClass("OrganizationOrganism");
			Zend_loader::loadClass("Organization");
			Zend_loader::loadClass("OrganismAttribute");
			
			$query=new QueryCreator();
			$query->addSelect(" o.organism_id,o.moid,o.name,o.sale_unit,o.default_inventory_cost,
								o.default_base_price_sale,o.currency_mark,d.link,o.created_by,o.date_created ");
			$query->addFrom(Organism::_tablename." as o ");
			$query->addJoin(" LEFT JOIN ".Document::_tablename." as d on o.picture_document_id=d.id");
			$query->addWhere(" o.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("organism",$result);
			
			//organizations_organisms
			$query	=	new QueryCreator();
			$query->addSelect("oo.org_organism_id,oo.base_price_sale,oa.*,o.name as organization_name");
			$query->addFrom(OrganizationOrganism::_tablename." as oo ");
			$query->addJoin("LEFT JOIN ".OrganismAttribute::_tablename." as oa ON oa.organism_attribute_id=oo.organism_attribute_id");
			$query->addJoin("LEFT JOIN ".Organization::_tablename." as o ON oo.org_id=o.org_id");
			$query->addWhere("oo.organism_id=".$id);
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			$this->assign("organization_organisms",$result);
		}
		$this->setLayout("admin");
		$this->render("systemPM_edit.tpl");
	}
	
	//SIZE MANAGEMENT
	public function systemSizeMAction(){
		Zend_loader::loadClass("Sizes");
		$sizes	=	new Sizes();
		$sizes->loadWhere("1=1","size");
		$this->assign("sizes",$sizes->toArray());
		
		$this->setNoLayout();
		$this->render("systemSizeM.tpl");
	}
    public function systemSizeMSaveListChangesAction(){
        Zend_loader::loadClass("Size");
        Zend_loader::loadClass("Sizes");
        Zend_loader::loadClass("SizeUtility");
       
       
        $sizes=new Sizes();
        $sizes->load();
       
        $ids           =    Array();
        $invalid_sizes =  Array();
       
        foreach($sizes->toArray() as $size){
            if($this->getParam('del_'.$size['size_code'])){
                if(SizeUtility::CheckOrganismAttributes($size))
                    $invalid_sizes[] = $size['size_code'].'-'.$size['size'];
                else
                    $ids[]    =    $size['size_code'];
            }
        }
       
        $sql    =    "DELETE FROM ".Size::_tablename."
                     WHERE size_code IN ('".implode("','",$ids)."');";
       
        $db = DBCon::instance();
        $db->executeCommand($sql);
       
        $this->assign("invalid_sizes",$invalid_sizes);
        $this->systemSizeMRefreshAction();
    }	
/* 	
	public function systemSizeMSaveListChangesAction(){
		Zend_loader::loadClass("Size");
		Zend_loader::loadClass("Sizes");
		
		$sizes=new Sizes();
		$sizes->load();
		
		$ids	=	Array();
		
		foreach($sizes->toArray() as $size){
			if($this->getParam('del_'.$size['size_code']))
				$ids[]	=	$size['size_code'];
		}
		
		$sql	=	"DELETE FROM ".Size::_tablename." 
					 WHERE size_code IN ('".implode("','",$ids)."');";
		
		$db = DBCon::instance();
		$db->executeCommand($sql);
		
		$this->systemSizeMRefreshAction();
	}
*/ 	
	public function systemSizeMEditAction(){
		Zend_loader::loadClass("Size");
		$code	=	$this->getParam("code");
		
		$size	=	new Size();
		if($code)
			$size->loadWhere("size_code='".$code."'");
		
		$this->assign("size",$size->toArray());
		
		$this->setNoLayout();
		$this->render("systemSizeM_popup.tpl");
	}
    public function systemSizeMAddAction(){
        Zend_loader::loadClass("Size");
        Zend_Loader::loadClass("AuthUtility");
       
        $auth = BaseAuth::getInstance();
        $params        =    $this->getParam();
       
        $hid        =    $params['hid'];
        $size_code    =    $params['size_code'];
        $size        =    $params['size'];
       
        $object        =    new Size();
        if($hid){
            $object->loadWhere("size_code='".$hid."'");
            $object->date_updated    =    date("Y-m-d H:i:s");
            $object->updated_by        =    $auth->id;
        }
        else{
            $object->date_created    =    date("Y-m-d H:i:s");
            $object->created_by        =    $auth->id;
        }
        $object->size_code    =    $size_code;
        $object->size        =    $size;
        $object->save();
       
        Zend_loader::loadClass("SizeUtility");
        SizeUtility::AddOrganismSizes($object);
       
        $this->systemSizeMRefreshAction();
    }	
/* 	
	public function systemSizeMAddAction(){
		Zend_loader::loadClass("Size");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$params		=	$this->getParam();
		
		$hid		=	$params['hid'];
		$size_code	=	$params['size_code'];
		$size		=	$params['size'];
		
		$object		=	new Size();
		if($hid){
			$object->loadWhere("size_code='".$hid."'");
			$object->date_updated	=	date("Y-m-d H:i:s");
			$object->updated_by		=	$auth->id;
		}
		else{
			$object->date_created	=	date("Y-m-d H:i:s");
			$object->created_by		=	$auth->id;
		}
		$object->size_code	=	$size_code;
		$object->size		=	$size;
		$object->save();
		
		$this->systemSizeMRefreshAction();
	}
*/ 	
	public function systemSizeMRefreshAction(){
		Zend_loader::loadClass("Sizes");
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		$sizes	=	new Sizes();
		$sizes->loadWhere("1=1","size");
		$this->assign("sizes",$sizes->toArray());
		
		$this->setLayout("admin");
		$this->render("systemSizeM.tpl");
	}
	
	//REPORT SUMMARY
	public function reportSummaryAction(){
	
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("reports.tpl");
	}
	public function reportSSearchAction(){
		$fma			=$this->getParam("fma");
		$date_from		=$this->getParam("dateFrom");
		$date_to		=$this->getParam("dateTo");
		if($this->getParam("nocaught")=='true')
			$this->assign("nocaught",1);
		if($this->getParam("aTac")=='true')
			$this->assign("aTac",1);
		if($this->getParam("doa")=='true')
			$this->assign("doa",1);
		if($this->getParam("rTac")=='true')
			$this->assign("rTac",1);
		if($this->getParam("doap")=='true')
			$this->assign("doap",1);
		if($this->getParam("reject")=='true')
			$this->assign("reject",1);
		if($this->getParam("rejectp")=='true')
			$this->assign("rejectp",1);
		$query=new QueryCreator();
		
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Fisher");
		$query->addSelect(" r.receipt_id,r.date_receipt,r.received_quantity,r.doa_quantity,
							r.reject_quantity,r.received_amount,r.currency_mark,
							o.organism_id,o.moid,o.scientific_name,o.name,
							org.trading_name,f.fishing_area_id,r.org_organism_id ");
		$query->addFrom(Receipt::_tablename." as r  ");
		$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.org_organism_id=r.org_organism_id ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as o on oo.organism_id=o.organism_id ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as org on org.org_id=r.supplier_org_id  ");
		$query->addJoin(" LEFT JOIN ".Fisher::_tablename." as f on r.supplier_fisher_id=f.fisher_id ");
		if($fma)
			$query->addWhere(" r.supplier_org_id=".$fma);
		if($date_from)
			$query->addWhere(" r.date_receipt>='".$date_from."' ");
		if($date_to)
			$query->addWhere(" r.date_receipt<='".$date_to."' ");
			
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		foreach($result as $key => $row){	//work the TAC for each row
			//get the tac the receipt belongs
			Zend_loader::loadClass("tacs");
			$tacs=new Tacs();
			$tacs->loadWhere(" organism_id=".$row['organism_id']." 
							AND fishing_area_id=".$row['fishing_area_id']."
							AND current=1 ");
			$tac_tmp=$tacs->toArray();
			$tac=$tac_tmp[0];
			//get all receipts that belongs in the tac
			$query= new QueryCreator();
			$query->addSelect(" r.receipt_id,r.date_receipt,sum(r.received_quantity) as sum_received,
							  sum(r.doa_quantity) as sum_doa,
							  sum(r.reject_quantity) as sum_reject ");
			$query->addFrom(Receipt::_tablename." as r ");
			$query->addJoin(" left join ".Fisher::_tablename." as f on r.supplier_fisher_id=f.fisher_id ");
			$query->addWhere(" r.date_receipt>='".$tac['date_start']."' ");
			$query->addWhere(" r.date_receipt<='".$tac['date_expire']."' ");
			$query->addWhere(" r.org_organism_id=".$row['org_organism_id']);
			$query->addWhere(" f.fishing_area_id=".$row['fishing_area_id']);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$temp_result_tac = $db->executeQuery($query->createSQL());
			$result_tac=$temp_result_tac[0];
			$limit=$tac['limit_quantity'];
			$limit90=$limit*.9;
			
			$result[$key]['tac_result']=($result_tac['sum_received']-$result_tac['sum_doa']-$result_tac['sum_reject']);
			$result[$key]['limit90']=$limit90;
			$result[$key]['limit']=$limit;
		}
		$this->assign("results",$result);
		$this->setNoLayout();
		$this->render("reportS_table.tpl");
	}
	public function reportSExcelAction(){
		
		$workbook = new Spreadsheet_Excel_Writer();
 
		/* Sending HTTP headers, this will popup a file save/open
		dialog in the browser when this file is run
		*/
		$workbook->send('Report_Summary'.time().'.xls');
		 
		// Create worksheets
		$worksheet1 =& $workbook->addWorksheet('Report_Summary');
				
		// Set header formating for Sheet 1
		$header =& $workbook->addFormat();
		$header->setBold();		// Make it bold
		$header->setColor('white');	// Make foreground color black
		$header->setFgColor("green");	// Set background color to green
		$header->setHAlign('center');	// Align text to center
		 
		// Write some data on Sheet 1
		$worksheet1->write(0, 0, 'ID', $header);
		$worksheet1->write(0, 1, 'Exporter', $header);
		$worksheet1->write(0, 2, 'Delivery Date', $header);
		$worksheet1->write(0, 3, 'Scientific Name', $header);
		$worksheet1->write(0, 4, 'Common Name', $header);
		$worksheet1->write(0, 5, 'DOA', $header);
		$worksheet1->write(0, 6, '%DOA', $header);
		$worksheet1->write(0, 7, 'Reject', $header);
		$worksheet1->write(0, 8, '%Reject', $header);
		$worksheet1->write(0, 9, 'Cost Per Specie', $header);
		$worksheet1->write(0, 10, 'Species approaching 90% Tac', $header);
		$worksheet1->write(0, 11, 'Species that reached TAC limit', $header);
		
		//--------- data section
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("Fisher");
		$query=new QueryCreator();
		$query->addSelect(" r.receipt_id,r.date_receipt,r.received_quantity,r.doa_quantity,
							r.reject_quantity,r.received_amount,r.currency_mark,
							o.organism_id,o.moid,o.scientific_name,o.name,
							org.trading_name,f.fishing_area_id,r.org_organism_id ");
		$query->addFrom(Receipt::_tablename." as r  ");
		$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." as oo on oo.org_organism_id=r.org_organism_id ");
		$query->addJoin(" LEFT JOIN ".Organism::_tablename." as o on oo.organism_id=o.organism_id ");
		$query->addJoin(" LEFT JOIN ".Organization::_tablename." as org on org.org_id=r.supplier_org_id  ");
		$query->addJoin(" LEFT JOIN ".Fisher::_tablename." as f on r.supplier_fisher_id=f.fisher_id ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		
		$y=1;
		foreach($result as $key => $row){	//work the TAC for each row
			
			//get the tac the receipt belongs
			Zend_loader::loadClass("tacs");
			$tacs=new Tacs();
			$tacs->loadWhere(" organism_id=".$row['organism_id']." 
							AND fishing_area_id=".$row['fishing_area_id']."
							AND current=1 ");
			$tac_tmp=$tacs->toArray();
			$tac=$tac_tmp[0];
			//get all receipts that belongs in the tac
			$query= new QueryCreator();
			$query->addSelect(" r.receipt_id,r.date_receipt,sum(r.received_quantity) as sum_received,
							  sum(r.doa_quantity) as sum_doa,
							  sum(r.reject_quantity) as sum_reject ");
			$query->addFrom(Receipt::_tablename." as r ");
			$query->addJoin(" left join ".Fisher::_tablename." as f on r.supplier_fisher_id=f.fisher_id ");
			$query->addWhere(" r.date_receipt>='".$tac['date_start']."' ");
			$query->addWhere(" r.date_receipt<='".$tac['date_expire']."' ");
			$query->addWhere(" r.org_organism_id=".$row['org_organism_id']);
			$query->addWhere(" f.fishing_area_id=".$row['fishing_area_id']);
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$temp_result_tac = $db->executeQuery($query->createSQL());
			$result_tac=$temp_result_tac[0];
			$limit=$tac['limit_quantity'];
			$limit90=$limit*.9;
			
			$row['tac_result']=($result_tac['sum_received']-$result_tac['sum_doa']-$result_tac['sum_reject']);
			$row['limit90']=$limit90;
			$row['limit']=$limit;
			
			/*$worksheet1->write($y,0,$row['receipt_id']);
			$worksheet1->write($y,1,$row['trading_name']);
			$worksheet1->write($y,2,$row['date_receipt']);
			$worksheet1->write($y,3,$row['scientific_name']);
			$worksheet1->write($y,4,$row['name']);
			$worksheet1->write($y,5,$row['received_quantity']);
			$worksheet1->write($y,6,$row['doa_quantity']);
			$worksheet1->write($y,7,(($row['doa_quantity']/$row['received_quantity'])*100));
			$worksheet1->write($y,8,$row['reject_quantity']);
			$worksheet1->write($y,9,(($row['reject_quantity']/$row['received_quantity'])*100));
			$worksheet1->write($y,10,(($row['received_amount']/$row['received_quantity'])*100));
			$worksheet1->write($y,11,($row['tac_result']/$row['limit90']));
			$worksheet1->write($y,12,($row['tac_result']/$row['limit']));*/
			$y++;
		}
		
		// Send the file to the browser
		$workbook->close();
	}
	//REPORT FMA ORS
	public function reportFMAORSAction(){
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("reportFMAORS.tpl");
	}
	public function reportFMAORSSearchAction(){
		$fma			=$this->getParam("fma");
		//print_r($this->getParam());die();
		$d1_array=explode('-',$this->getParam("dateFrom"));
		$d2_array=explode('-',$this->getParam("dateTo"));
		
		$date_start=strtotime($this->getParam("dateFrom"));
		$date_end=strtotime($this->getParam("dateTo"));
		
		$tdate_start=mktime(1,1,1,$d1_array[1],1,$d1_array[0]);	
		$result	=array();
		$months=array();
		while($tdate_start<=$date_end){
			//for the date start
			$tdate_end=strtotime('+1 month -1 day',$tdate_start);
			if($tdate_start>$date_start)
				$temp=$tdate_start;
			else
				$temp=$date_start;
			
			//for the date end
			if($date_end<$tdate_end)
				$temp2=$date_end;
			else
				$temp2=$tdate_end;
			
			//reporting here
			Zend_loader::loadClass("Organization");
			Zend_loader::loadClass("Shipment");
			Zend_loader::loadClass("Receipt");
			$query=new QueryCreator();
			$query->addSelect(" o.org_id, o.trading_name, sum(IFNULL(r.received_quantity,0)) as received_quantity,
								r.date_receipt, s.shipment_id, 
								sum(IFNULL (s.total_quantity,0)) as shipped_quantity,
								s.date_shipment ");
			$query->addFrom(Organization::_tablename." AS o ");
			$query->addJoin(" LEFT JOIN ".Shipment::_tablename." AS s ON s.org_id = o.org_id ");
			$query->addJoin(" LEFT JOIN ".Receipt::_tablename." AS r ON o.org_id = r.supplier_org_id ");
			if($fma)
				$query->addWhere(" o.org_id=".$fma." ");
				
			$query->addWhere(" ((s.date_shipment >= '".date("Y-m-d H:i:s",$temp)."' AND s.date_shipment <= '".date("Y-m-d H:i:s",$temp2)."')
							OR (r.date_receipt >= '".date("Y-m-d H:i:s",$temp)."' AND r.date_receipt <= '".date("Y-m-d H:i:s",$temp2)."')) ");
			$query->addwhere(" 1=1 Group by o.org_id ");
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$tresult = $db->executeQuery($query->createSQL());
			//print_r($tresult);die();
			
			foreach($tresult as $row){
				$result[$row['org_id']]['trading_name']=$row['trading_name'];
				$result[$row['org_id']][date("F-Y",$temp)]['received_quantity']=$row['received_quantity'];
				$result[$row['org_id']][date("F-Y",$temp)]['shipped_quantity']=$row['shipped_quantity'];
				$result[$row['org_id']]['total_received']+=$row['received_quantity'];
				$result[$row['org_id']]['total_shipped']+=$row['shipped_quantity'];
			}
			$months[date("F-Y",$temp)]=date("F-Y",$temp);
			
			//adding 1month
			$tdate_start=strtotime('+1 month',$tdate_start);
			$tdate_end=strtotime('+1 month',$tdate_start);
		}
		//print_r($months);
		//print_r($result);die();
		$this->assign("months",$months);
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("reportFMAORS_table.tpl");
	}
	
	public function exporterShipmentSummaryReportAction() {
	
		$auth = BaseAuth::getInstance();
		$userValue = $this->getParam("user");

		// country
		if(!isset($userValue["country"])) {
			Zend_Loader::loadClass("Organizations");
			$org = new Organizations();
			$orgCountries = $org->getOrgTypeCountries('Exporter');
			$this->assign("countries",$orgCountries);
		}
		
		// province
		if(!isset($userValue["province"])) {
			Zend_Loader::loadClass("Organizations");
			$org = new Organizations();
			$orgProvinces = $org->getOrgTypeProvinces('Exporter');
			$this->assign("provinces",$orgProvinces);
		}

		// exporter
		if(!isset($userValue["exporter"])) {
			Zend_Loader::loadClass("Organizations");
			$exporters = new Organizations();
			$exporters->loadWhere(" org_type = 'Exporter'");
			$this->assign("exporters",$exporters->toArray());
		}
		
		// user values
		$searchCountry = $userValue["country"];
		$searchProvince = $userValue["province"];
		$searchExporter = $userValue["exporter"];
		$searchDateFrom = $userValue["dateFrom"];
		$searchDateTo = $userValue["dateTo"];

		// get the shipments
		Zend_Loader::loadClass("Shipments");
		$shipment = new Shipments();
		$shipments = $shipment->getMonthlyListOfShipmentSummary('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);

		// get the DOAs
		Zend_Loader::loadClass("Receipts");
		$receipt = new Receipts();
		$receipts = $receipt->getMonthlyListOfDOASummary('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);

		// get the rejects
		Zend_Loader::loadClass("ReceiptRejects");
		$reject = new ReceiptRejects();
		$rejects = $reject->getMonthlyListOfRejectSummary('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);
	
		// get the calendar
		Zend_Loader::loadClass("CalendarYearMonthNames");
		$calendar = new CalendarYearMonthNames();
		$yearmonth = $calendar->getYearMonthNames($searchDateFrom,$searchDateTo);
		
		if((isset($userValue["shipment_summary"]) &&  $userValue["shipment_summary"] == 'on') ||
			(isset($userValue["organism_shipment"]) &&  $userValue["organism_shipment"] == 'on')) {
			$summary = $shipment->getMonthlyListOfShipmentDetail('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);
			$org = $shipment->getMonthlyCountOfSupplierDetail('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);
			$total = $shipment->getMonthlySumOfShipmentDetail('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);
			$this->assign("details",$summary);
			$this->assign("orgs",$org);
			$this->assign("totals",$total);
		}
		if(isset($userValue["shipment_summary"]) &&  $userValue["shipment_summary"] == 'on') {
			$this->assign("show_shipment_summary",'1');
		}
		else {
			$this->assign("show_shipment_summary",'0');
		}
		if(isset($userValue["organism_shipment"]) &&  $userValue["organism_shipment"] == 'on') {
			$this->assign("show_organism_shipment",'1');
		}
		else {
			$this->assign("show_organism_shipment",'0');
		}
		

		$this->setNoLayout();
		$this->assign("shipments",$shipments);
		$this->assign("doas",$receipts);
		$this->assign("rejects",$rejects);
		$this->assign("yearmonth",$yearmonth);
		
		// echo "<pre>";print_r($userValue["shipment_summary"]);echo "</pre>";die();

		$reload = $this->getParam("reload");
		if ($reload == '1') {
			$this->render('exporterShipmentSummaryReportSub.tpl');
		}
		else {
			$this->render('exporterShipmentSummaryReport.tpl');
		}
	}
 
	//MESSAGING
	public function mailAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
		
		//msgs
		Zend_loader::loadClass("EmailMessage");
		Zend_loader::loadClass("Organization");
		$query=new QueryCreator();
		$query->addSelect(" e.subject,e.message_id,e.read,o.name as source,e.date_sent ");
		$query->addFrom(EmailMessage::_tablename." as e  ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.id=e.source_org_id ");
		$query->addWhere(" e.deleted=0 ");
		$query->addWhere(" e.recipient_org_id=".$auth->org_id."  Order by e.date_sent DESC ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("msgs",$result);
		
		//senders
		$query=new QueryCreator();
		$query->addSelect(" distinct o.name,o.id ");
		$query->addFrom(EmailMessage::_tablename." as e  ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.id=e.source_org_id ");
		$query->addWhere(" e.recipient_org_id=".$auth->org_id);
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$senders = $db->executeQuery($query->createSQL());
		$this->assign("senders",$senders);
		
		$this->setNoLayout();
		$this->render("mail.tpl");
	}
	public function mailEditAction(){
		$id=$this->getParam("id");
		if($id){
			Zend_loader::loadClass("EmailMessage");
			$msg=new EmailMessage();
			$msg->load($id);
			$msg->read=1;
			$msg->date_read=date("Y-m-d H:i:s");
			$msg->save();
			$this->assign("msg",$msg->toArray());
			$this->assign("id",$id);
		}
		
		//sendtos
		Zend_loader::loadClass("Organizations");
		$orgs=new Organizations();
		$orgs->loadWhere(" 1=1 ");
		$this->assign("sendtos",$orgs->toArray());
		
		$this->setNoLayout();
		$this->render("mail_popup.tpl");
	}
	public function mailSaveListChangesAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
	
		Zend_loader::loadClass("EmailMessages");
		Zend_loader::loadClass("EmailMessage");
		$msgs=new EmailMessages();
		$msgs->loadWhere(" recipient_org_id=".$auth->org_id);
		
		foreach($msgs->toArray() as $msg){
			if($this->getParam('del_'.$msg['id'])){
				$m=new EmailMessage();
				$m->load($msg['id']);
				$m->delete();
			}
		}
		//$this->_redirect("/admin/mailRefresh");
		$this->mailRefreshAction();
	}
	public function mailAddAction(){
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
	
		Zend_loader::loadClass("EmailMessage");
		$msg=new EmailMessage();
		$msg->source_org_id		=$auth->org_id;
		$msg->subject			=$this->getParam("subject");
		$msg->message			=$this->getParam("message");
		$msg->date_sent			=date("Y-m-d H:i:s");
		//$msg->recipient_org_id	=$this->getParam("sendto");
		$msg->recipient_org_id	=$auth->org_id;
		$msg->save();
		$msg->message_id=$msg->id;
		$msg->save();
		//$this->_redirect("/admin/mailRefresh");
		$this->mailRefreshAction();
	}	
	public function mailRefreshAction(){
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		//msgs
		Zend_loader::loadClass("Organization");
		Zend_loader::loadClass("EmailMessage");
		$query=new QueryCreator();
		$query->addSelect(" e.subject,e.message_id,e.read,o.name as source,e.date_sent ");
		$query->addFrom(EmailMessage." as e  ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.id=e.source_org_id ");
		$query->addWhere(" e.deleted=0 ");
		$query->addWhere(" e.recipient_org_id=".$auth->org_id."  Order by e.date_sent DESC ");
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("msgs",$result);
		
		//senders
		$query=new QueryCreator();
		$query->addSelect(" distinct o.name,o.id ");
		$query->addFrom(EmailMesage::_tablename." as e  ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.id=e.source_org_id ");
		$query->addWhere(" e.recipient_org_id=".$auth->org_id);
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$senders = $db->executeQuery($query->createSQL());
		$this->assign("senders",$senders);
		
		$this->setLayout("admin");
		$this->render("mail.tpl");
	}
	
	public function selectFmaAction(){
		Zend_Loader::loadClass("Organizations");
		Zend_Loader::loadClass("CodesUtility");
		
		$search = $this->getParam("search");
		$country_code = $search['fma'];
		
		$fma = new Organizations();
		$fmaValues = $fma->getOrganizationSupplier(CodesUtility::USER_TYPE_FMA,array(),$country_code);
		
		$this->assign("fma",$fmaValues);
		$this->setNoLayout();
		$this->render("selectfma.tpl");
	}
	
	public function selectExporterAction(){
		Zend_Loader::loadClass("Organizations");
		Zend_Loader::loadClass("CodesUtility");
		
		$search = $this->getParam("search");
		$country_code = $search['fma'];
		
		$fma = new Organizations();
		$fmaValues = $fma->getOrganizationSupplier(CodesUtility::USER_TYPE_EXPORTER,array(),$country_code);
		
		$this->assign("fma",$fmaValues);
		$this->setNoLayout();
		$this->render("selectexporter.tpl");
	}
	
	public function catchsummaryResultAction(){
	
		Zend_Loader::loadClass("ReportsUtility");
		Zend_Loader::loadClass("CartUtility");
	
		$param = $this->getParam("search");
		
		$cart = CartUtility::getToexcelCart();
	
		
		$catchsummary = ReportsUtility::catchSummary($param);
		
		$cart->items = $catchsummary;
		$cart->param = $param;
		
		$this->assign("param",$param);
		$this->assign("results",$catchsummary);
		$this->setNoLayout();
		$this->render("catchsummaryresult.tpl");
	}
	
	public function saveorssummaryAction(){
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("ExcelWriter");
		
		$cart = CartUtility::getToexcelCart();
		$items = $cart->items;
		
		$filename = mt_rand();
		$excelwriter = new ExcelWriter();
		$excelwriter->ExcelWriter($filename.".xls");
		
		$head = $items[0];
		$detail = $items[1];
		foreach($head as $key => $value) {
			
			$excelwriter->writeRow();
			$excelwriter->writeCol($key);
			$excelwriter->writeRow();
			$excelwriter->writeCol("Exporter");
			foreach($value['month'] as $monthkey => $monthvalue) {
				$excelwriter->writeCol($monthvalue,3);			
			}
			$excelwriter->writeCol("Total",3);
			$excelwriter->writeRow();
			$excelwriter->writeCol(" ");
			foreach($value['month'] as $monthkey => $monthvalue) {
				
				$excelwriter->writeCol("Ordered");
				$excelwriter->writeCol("Delivered");
				$excelwriter->writeCol("Diff");
			}
			
			$excelwriter->writeCol("Ordered");
			$excelwriter->writeCol("Delivered");
			$excelwriter->writeCol("Diff");
			
			$excelwriter->writeRow();
			$totalOrder = 0;
			$totalDelivered = 0;
			$totalDiff = 0;
			
			foreach($detail as $detaikey => $detailValue) {
				
				$excelwriter->writeCol($detailkey);
				$rtotalOrder = 0;
				$rtotalDelivered = 0;
				$rtotalDiff = 0;
				foreach($value['month'] as $monthkey => $monthvalue) {
				
					$order = 0;
					$delivered = 0;
					$diff = 0;
					foreach($detailValue['month'][$key][$monthvalue]['order'] as $orderkey => $ordervalue) {
						$order= $order + $ordervalue;
					}
					
					$delivered = $detailValue['month'][$key][$monthvalue]['delivered'];
					$diff = $order - $delivered;
					
					$excelwriter->writeCol($order);
					$excelwriter->writeCol($delivered);
					$excelwriter->writeCol($diff);
					
					$totalOrder = $totalOrder+$order;
					$totalDelivered = $totalDelivered+$delivered;
					$totalDiff = $totalDiff+$diff;
					
					$rtotalOrder = $rtotalOrder+$order;
					$rtotalDelivered = $rtotalDelivered+$delivered;
					$rtotalDiff = $rtotalDiff+$diff;
					
					$m[$monthkey.$key]['Order'] = $m[$monthkey.$key]['Order']+$order;
					$m[$monthkey.$key]['Delivered'] = $m[$monthkey.$key]['Delivered']+$delivered;
					$m[$monthkey.$key]['Diff'] = $m[$monthkey.$key]['Diff']+$diff;
					
				}
				//row total
				$excelwriter->writeCol($rtotalOrder);
				$excelwriter->writeCol($rtotalDelivered);
				$excelwriter->writeCol($rtotalDiff);
				//
				$excelwriter->writeRow();
			}
			
			
			// column total
			$excelwriter->writeCol("Total");
			foreach($value['month'] as $monthkey => $monthvalue) {
				
				$excelwriter->writeCol($m[$monthkey.$key]['Order']);
				$excelwriter->writeCol($m[$monthkey.$key]['Delivered']);
				$excelwriter->writeCol($m[$monthkey.$key]['Diff']);
			}
			$excelwriter->writeCol($totalOrder);
			$excelwriter->writeCol($totalDelivered);
			$excelwriter->writeCol($totalDiff);
			// 
			
			$excelwriter->writeRow();
		}
		
		$this->assign("host",$_SERVER['HTTP_HOST']);
		$this->setNoLayout();
		$this->assign("filename",$filename.".xls");
		$this->render("downloadfile.tpl");
		
	}
	
	public function savecatchAction() {
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("ExcelWriter");
		Zend_Loader::loadClass("Zend_Session_Namespace");
		
		$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
		$uploadPath = $client_session->CLIENT_WWW.$this->getConfig()->paths->upload;
		
		$excelwriter = new ExcelWriter();
		//echo $uploadPath;
		$filename = mt_rand();
		$excelwriter->ExcelWriter($filename.".xls");
		
		$cart = CartUtility::getToexcelCart();
		$items = $cart->items;
		$param = $cart->param;
		$data = array();
		$tacpercent = 0;
		$taclimit = 0;
		
		$header[] = 'Fishsery Managment Area';
		$header[] = 'Fisher';
		$header[] = 'Month/Year';
		$header[] = 'Moid';
		$header[] = 'Scientific Name';
		$header[] = 'Common Name';
		if($param['nocaught'])
			$header[] = '# Caught';
		if($param['doa'])
			$header[] = 'DOA';
		if($param['percentdoa']) 
			$header[] = '% DOA';
		if($param['reject'])
			$header[] = 'Reject';
		if($param['percentreject']) 
			$header[] = '% Reject';
		if($param['approaching']) 
			$header[] = 'Species approaching 90% TAC';
		if($param['taclimit'])
			$header[] = 'Species that reached TAC Limit';
			
		$excelwriter->writeLine($header);
		
		foreach($items as $key => $value){
			$line = array();
			$line[] = $value['FMA'];
			$line[] = $value['fishersName'];
			$line[] = $value['mymonth'].'/'.$value['myyear'];
			$line[] = $value['moid'];
			$line[] = $value['scientific_name'];
			$line[] = $value['common_name'];
			if($param['nocaught'])
				$line[] = $value['caught'];
			if($param['doa'])
				$line[] = $value['doa'];
			if($param['percentdoa']) {
				$pdoa = ($value['doa']/$value['caught']) * 100;
				$line[] = $pdoa;
			}
			if($param['reject'])
				$line[] = $value['reject'];
				
			if($param['percentreject']) {
				$line[] = ($value['reject']/$value['caught']) * 100;
			}
			
			if($param['approaching']) {
				if($value['tacpercent'] >= 90) {
					$line[] = 'Y';
					$tacpercent = $tacpercent+1;
				} else {
					$line[] = 'N';
				}
			}
			
			if($param['taclimit']){
				if($value['taclimit']) {
					$line[] = 'Y';
					$taclimit = $taclimit+1;
				} else {
					$line[] = 'N';
				}
			}
			
			$excelwriter->writeLine($line);
			
		}
		//copy("../docroot/".$filename.".xls", $uploadPath.'/'.$filename.'.xls');
		//ECHO $_SERVER['HTTP_HOST'];
		$this->assign("host",$_SERVER['HTTP_HOST']);
		$this->setNoLayout();
		$this->assign("filename",$filename.".xls");
		$this->render("downloadfile.tpl");
		
		//$cart->items = null;
		//$cart->param = null;
		
	}
	
	public function catchsummaryReportAction(){
		
		Zend_Loader::loadClass("Countries");
		
		$countries = new Countries();
		$countryValue = $countries->getCountries(array(0=>'country_code',1=>'country'));
		
		$this->assign("country",$countryValue);
		$this->setNoLayout();
		$this->render("catchsummaryreport.tpl");
	}

	public function mailSearchAction(){
		$sender=$this->getParam("sender");
		$fdate =$this->getParam("f_date");
		$tdate =$this->getParam("t_date");
		
		Zend_Loader::loadClass("AuthUtility");
		$auth = BaseAuth::getInstance();
		
		//msgs
		Zend_loader::loadClass("EmailMessage");
		Zend_loader::loadClass("Organization");
		$query=new QueryCreator();
		$query->addSelect(" e.subject,e.message_id,e.read,o.name as source,e.date_sent ");
		$query->addFrom(EmailMessage::_tablename." as e  ");
		$query->addJoin(" left join ".Organization::_tablename." as o on o.id=e.source_org_id ");
		$query->addWhere(" e.deleted=0 ");
			if($fdate)
			$query->addWhere(" e.date_sent>='".$fdate."' ");
		if($tdate)
			$query->addWhere(" e.date_sent<='".$tdate."' ");
		if($sender)
			$query->addWhere(" e.source_org_id=".$sender);
		$query->addWhere(" e.recipient_org_id=".$auth->org_id."  Order by e.date_sent DESC ");
	
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("msgs",$result);
		$this->setNoLayout();
		$this->render("mail_table.tpl");
	}

	//REPORT FMA tracking shipments
	public function reportFMATSAction(){
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("reportFMATS.tpl");
	}
	public function reportFMATSSearchAction(){
		$fma			=$this->getParam("fma");
		$date_from		=$this->getParam("dateFrom");
		$date_to		=$this->getParam("dateTo");

		Zend_loader::loadClass("Shipment");
		Zend_loader::loadClass("purchaseOrder");
		Zend_loader::loadClass("purchaseOrderDetail");
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("Shipmethod");
		Zend_loader::loadClass("Organization");
		$query=new QueryCreator();

		$query->addSelect(" distinct s.shipment_id,od.po_id,s.date_shipment,org2.trading_name as ship_name,org2.id as ship_id, r.doa_quantity,r.reject_quantity,r.received_quantity,r.received_amount,
						sm.name,org.trading_name ");
		$query->addFrom(Shipment::_tablename." as s ");
		$query->addJoin(" left join ".purchaseOrder::_tablename." as o on s.po_id=o.po_id  ");
		$query->addJoin(" left join ".purchaseOrderDetail::_tablename." as od on od.po_id=o.po_id and od.supplier_org_id=s.customer_org_id ");
		$query->addJoin(" left join ".Receipt::_tablename." as r on r.reference_id=od.po_id and r.supplier_org_id=s.customer_org_id ");
		$query->addJoin(" left join ".Shipmethod::_tablename." as sm on s.ship_method_id=sm.ship_method_id ");
		$query->addJoin(" left join ".Organization::_tablename." as org on org.id=sm.freight_org_id  ");
		$query->addJoin(" left join ".Organization::_tablename." as org2 on org2.id=s.org_id ");
		if($fma)
			$query->addWhere(" s.org_id=".$fma);
		if($date_from)
			$query->addWhere(" s.date_shipment>='".$date_from."' ");
		if($date_to)
			$query->addWhere(" s.date_shipment<='".$date_to."' ");
		
		$query->addWhere(" r.source='PO' order by ship_name ");
		
		$db = DBCon::instance();
		//echo $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		$this->assign("results",$result);
		
		$this->setNoLayout();
		$this->render("reportFMATS_table.tpl");
	}
	//REPORT Shipment Summary
	public function reportShipmentSummaryAction(){
		//for the searching countries,organism_groups
		Zend_loader::loadClass("Countries");
		$countries=new Countries();
		$countries->loadWhere(" 1=1 ");
		$this->assign("countries",$countries->toArray());
		
		$this->setNoLayout();
		$this->render("reportFMASS.tpl");
	}
	public function reportShipmentSummarySearchAction(){
		Zend_loader::loadClass("Shipment");
		Zend_loader::loadClass("purchaseOrder");
		Zend_loader::loadClass("purchaseOrderDetail");
		Zend_loader::loadClass("Receipt");
		Zend_loader::loadClass("OrganizationOrganism");
		Zend_loader::loadClass("Organism");
		Zend_loader::loadClass("Organization");
		
		$fma			=$this->getParam("fma");
		if($this->getParam("summary")=='true')
			$this->assign("summary",1);
		if($this->getParam("organism")=='true')
			$this->assign("organism",1);
		//print_r($this->getParam());die();
		$d1_array=explode('-',$this->getParam("dateFrom"));
		$d2_array=explode('-',$this->getParam("dateTo"));
		
		$date_start=strtotime($this->getParam("dateFrom"));
		$date_end=strtotime($this->getParam("dateTo"));
		
		$tdate_start=mktime(1,1,1,$d1_array[1],1,$d1_array[0]);	
		$result	=array();
		$months=array();
		$nshipments=array();
		$norganisms=array();
		while($tdate_start<=$date_end){
			//for the date start
			$tdate_end=strtotime('+1 month -1 day',$tdate_start);
			if($tdate_start>$date_start)
				$temp=$tdate_start;
			else
				$temp=$date_start;
			
			//for the date end
			if($date_end<$tdate_end)
				$temp2=$date_end;
			else
				$temp2=$tdate_end;
			
			//print_r(date("Y-m-d H:i:s",$temp).'-'.date("Y-m-d H:i:s",$temp2).'++++++');
			$query=new QueryCreator();
			$query->addSelect(" sum( DISTINCT s.shipment_id ) as sshipid , r.doa_quantity, r.reject_quantity, 
			r.received_quantity, sum( DISTINCT o.id ) as soid , o.name, ord.po_id, 
			avg( r.doa_quantity ) AS avg_doa, avg( r.reject_quantity ) AS avg_reject,avg( r.received_quantity ) AS avg_received,
 			r.received_amount, s.total_amount ");
			$query->addFrom(Shipment::_tablename." AS s ");
			$query->addJoin(" LEFT JOIN ".purchaseOrder::_tablename." AS ord ON ord.po_id = s.po_id ");
			$query->addJoin(" LEFT JOIN ".purchaseOrderDetail::_tablename." AS od ON od.po_id = ord.po_id ");
			$query->addJoin(" LEFT JOIN ".Receipt::_tablename." AS r ON r.reference_id = s.po_id ");
			$query->addJoin(" LEFT JOIN ".OrganizationOrganism::_tablename." AS oo ON oo.org_organism_id = od.org_organism_id ");
			$query->addJoin(" LEFT JOIN ".Organism::_tablename." AS o ON o.id = oo.organism_id ");
			//$query->addWhere(" s.org_id=od.supplier_org_id ");
			if($fma)
				$query->addWhere(" s.org_id=".$fma);
			if($tdate_start)
				$query->addWhere(" s.date_shipment>='".date("Y-m-d H:i:s",$tdate_start)."' ");
			if($tdate_end)
				$query->addWhere(" s.date_shipment<='".date("Y-m-d H:i:s",$tdate_end)."' ");
				
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$tresult = $db->executeQuery($query->createSQL());
			
			$result[date("F-Y",$temp)]=$tresult[0];
			$months[date("F-Y",$temp)]=date("F-Y",$temp);
			
			//number of shipments
			$query=new QueryCreator();
			$query->addSelect(" count( distinct s.shipment_id) as cnt,o.org_id,o.trading_name  ");
			$query->addFrom(Shipment::_tablename." as s ");
			$query->addJoin(" left join ".Organization::_tablename." as o on s.org_id=o.org_id ");
			if($tdate_start)
				$query->addWhere(" s.date_shipment>='".date("Y-m-d H:i:s",$tdate_start)."' ");
			if($tdate_end)
				$query->addWhere(" s.date_shipment<='".date("Y-m-d H:i:s",$tdate_end)."' ");
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$tshipment= $db->executeQuery($query->createSQL());
			if($tshipment[0]['org_id']!=null)
				$nshipments[$tshipment[0]['org_id']][date("F-Y",$temp)]=$tshipment[0];
			
			//number of orgs
			$query=new QueryCreator();
			$query->addSelect(" s.shipment_id, count( DISTINCT oo.organism_id ) as cnt ,o.organism_id, o.name,org.org_id,org.trading_name ");
			$query->addFrom(Shipment::_tablename." AS s");
			$query->addJoin("LEFT JOIN ".purchaseOrder::_tablename." AS ord ON ord.po_id = s.po_id");
			$query->addJoin("LEFT JOIN ".purchaseOrderDetail::_tablename." AS od ON od.po_id = ord.po_id");
			$query->addJoin("LEFT JOIN ".OrganizationOrganism::_tablename." AS oo ON od.org_organism_id = oo.org_organism_id");
			$query->addJoin("LEFT JOIN ".Organism::_tablename." AS o ON oo.organism_id = o.organism_id");
			$query->addJoin("LEFT JOIN ".Organization::_tablename." AS org ON org.org_id = s.org_id");
			//$query->addWhere(" s.org_id = od.supplier_org_id ");
			if($tdate_start)
				$query->addWhere(" s.date_shipment>='".date("Y-m-d H:i:s",$tdate_start)."' ");
			if($tdate_end)
				$query->addWhere(" s.date_shipment<='".date("Y-m-d H:i:s",$tdate_end)."' ");
			$db = DBCon::instance();
			//echo $query->createSQL();die();
			$torgs= $db->executeQuery($query->createSQL());
			if($torgs[0]['org_id']!=null)
				$norganisms[$torgs[0]['org_id']][date("F-Y",$temp)]=$torgs[0];
			//adding 1month
			$tdate_start=strtotime('+1 month',$tdate_start);
			$tdate_end=strtotime('+1 month',$tdate_start);
		}
		//print_r($nshipments);die();
		//print_r($norganisms);die();
		//print_r($result);die();
		$this->assign("months",$months);
		$this->assign("results",$result);
		$this->assign("nshipments",$nshipments);
		$this->assign("norganisms",$norganisms);
		
		$this->setNoLayout();
		$this->render("reportFMASS_table.tpl");
	}
 
	public function imgOrganismSetPicAction(){
		Zend_loader::loadClass("Organisms");
		$orgs=new Organisms();
		$orgs->loadWhere(" 1=1 ");
		
		Zend_loader::loadClass("Document");
		$doc=new Document();
		
		Zend_loader::loadClass("Organism");
		$org=new Organism();
		foreach($orgs->toArray() as $row){
			//print_r(str_replace(' ','-',$row['scientific_name']));print_r(" -------------------  ");
			$doc->clear();
			$doc->link='/app/default/www/images/thumbs/'.str_replace(' ','-',$row['scientific_name']).'.jpg';
			$doc->title="pic";
			$doc->save();
			
			$org->clear();
			$org->load($row['id']);
			$org->picture_document_id=$doc->id;
			$org->save();
		}
		die();
	}
	
	public function exporterorssummaryAction(){
		Zend_Loader::loadClass("Countries");
		
		$countries = new Countries();
		$countryValue = $countries->getCountries(array(0=>'country_code',1=>'country'));
		
		$this->assign("country",$countryValue);
	
		$this->setNoLayout();
		$this->render();
	}
	
	public function exporterorssummaryresultAction(){
		Zend_Loader::loadClass("ReportsUtility");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("CartUtility");
		
		$cart = CartUtility::getToexcelCart();
		
		
		$param = $this->getParam("search");
		
		$data = ReportsUtility::orsSummary(CodesUtility::USER_TYPE_EXPORTER,$param);
		
		$cart->items = $data;
	
		$this->assign("header",$data[0]);
		$this->assign("detail",$data[1]);
		$this->setNoLayout();
		$this->render('orssummaryresult.tpl');
	}	

	public function imgFisherSetPicAction(){
		Zend_loader::loadClass("Fishers");
		Zend_loader::loadClass("Fisher");
		Zend_loader::loadClass("Document");
		Zend_loader::loadClass("Contact");
		$fishers=new Fishers();
		$fishers->loadWhere(" 1=1 ");
		
		$doc=new Document();
		$fisher=new Fisher();
		$contact=new Contact();
		foreach($fishers->toArray() as $row){
			$doc->clear();
			$fisher->clear();
			$fisher->load($row['id']);
			$contact->clear();
			if($fisher->contact_id){
				$contact->load($fisher->contact_id);
				if($contact->photo_document_id)
					$doc->load($contact->photo_document_id);
			}
			$doc->link='/app/default/www/images/thumbs/fisher/'.$row['first_name'].'-'.$row['last_name'].'.jpg';
			$doc->title="fisher pic";
			$doc->save();
			$contact->photo_document_id=$doc->id;
			$contact->save();
			$fisher->contact_id=$contact->contact_id;
			$fisher->save();
		}	
	}
 
	public function exporterTrackingShipmentReportAction() {

		$auth = BaseAuth::getInstance();
		$userValue = $this->getParam("user");
		$reload = $this->getParam("reload");

		// country
		if(!isset($userValue["country"])) {
			Zend_Loader::loadClass("Organizations");
			$org = new Organizations();
			$orgCountries = $org->getOrgTypeCountries('Exporter');
			$this->assign("countries",$orgCountries);
		}
		
		// province
		if(!isset($userValue["province"])) {
			Zend_Loader::loadClass("Organizations");
			$org = new Organizations();
			$orgProvinces = $org->getOrgTypeProvinces('Exporter');
			$this->assign("provinces",$orgProvinces);
		}

		// exporter
		if(!isset($userValue["exporter"])) {
			Zend_Loader::loadClass("Organizations");
			$exporters = new Organizations();
			$exporters->loadWhere(" org_type = 'Exporter'");
			$this->assign("exporters",$exporters->toArray());
		}
		
		$searchPage = '0';
		if ($reload == '1') {
			// user values
			$searchCountry = $userValue["country"];
			$searchProvince = $userValue["province"];
			$searchExporter = $userValue["exporter"];
			$searchDateFrom = $userValue["dateFrom"];
			$searchDateTo = $userValue["dateTo"];
			$searchPage = $this->getParam("page");
			
			Zend_Loader::loadClass("Shipments");
			$shipment = new Shipments();

			if(strlen($searchPage) <= 0) { $searchPage = '0'; }

			// get the exporter in focus
			$exp = $shipment->getShipmentExporters('Exporter',$searchExporter,$searchPage, 1, $searchCountry,$searchProvince,$searchDateFrom,$searchDateTo);
			
			// print_r($exp->toArray());die();
			if(isset($searchExporter) && isset($exp[0]) && $searchExporter <= 0) {
				$searchExporter = $exp[0]['org_id'];
			}

			// get the shipments
			$shipments = $shipment->getShipmentTrackingSummary('Exporter',$searchCountry,$searchProvince,$searchExporter,$searchDateFrom,$searchDateTo);
			$this->assign("shipments",$shipments);

			// get the shipment dates of the exporter
			$shipDates = $shipment->getOrgShipmentDates('Exporter',$searchExporter);
			$this->assign("shipDates",$shipDates);

			// get the count of suppliers for this filter
			$cexp = $shipment->getShipmentExporters('Exporter',0,0,0, $searchCountry,$searchProvince,$searchDateFrom,$searchDateTo);
			$this->assign("curr_exporter",$exp);
			$this->assign("exporters",$cexp);

			// get the importers for this exporter
			$imp = $shipment->getOrgShipmentOrgs('Exporter',$searchExporter,$searchCountry,$searchProvince,$searchDateFrom,$searchDateTo);
			$this->assign("importers",$imp);
			
			// get the count of importers
			$impcount = $shipment->getCountOrgShipmentOrgs('Exporter',$searchExporter,$searchCountry,$searchProvince,$searchDateFrom,$searchDateTo);
			$this->assign("importer_count",$impcount);

		}		


		
		// default date current year
		$dt = getdate();

		$this->setNoLayout();
		$this->assign("dtFrom",date("Y-m-d"));
		$this->assign("dtTo",date("Y-m-d"));
		$this->assign("page",$searchPage);
		$this->assign("reload",$reload);

		if ($reload == '1') {
			$this->render('exporterShipmentTrackingReportSub.tpl');
		}
		else {
			$this->render('exporterShipmentTrackingReport.tpl');
		}

	}
	
	public function backupAction(){
		Zend_Loader::loadFile("DumpUtility.php");
		Zend_Loader::loadClass("DumpUtility");
		
		$db = $this->getConfig()->database->dbname;
		$dbar = explode("_",$db);
		
		$host = $this->getConfig()->client->host;
		
		$dbprefix = $dbar[0];
		
		$curdir = getcwd();
		if(stripos($curdir,"docroot") > 0) {
			//$directory = "uploadfile/";
			$directory = "../application/default/www/images/";
		} else {
			//$directory = "docroot/uploadfile/";
			$directory = "application/default/www/images/";
		}
		
		
		$inventory = DumpUtility::_mysqldump($dbprefix."_inventory",'','',$dbprefix."_inventory");
		$ref = DumpUtility::_mysqldump($dbprefix."_ref",'','',$dbprefix."_ref");
		$trade = DumpUtility::_mysqldump($dbprefix."_trade",'','',$dbprefix."_trade");
		$entity = DumpUtility::_mysqldump($dbprefix."_entity",'','',$dbprefix."_entity");
		$sales = DumpUtility::_mysqldump($dbprefix."_sales",'','',$dbprefix."_sales");
		
		$date = getDate();
		
		$filename = $date[0].'.sql';
		$handle = fopen($directory.$filename, 'w+');
		
		foreach($inventory as $key => $value){
			fwrite($handle,$value);
		}
		
		foreach($ref as $key => $value){
			fwrite($handle,$value);
		}
		
		foreach($trade as $key => $value){
			fwrite($handle,$value);
		}
		
		foreach($entity as $key => $value){
			fwrite($handle,$value);
		}
		
		foreach($sales as $key => $value){
			fwrite($handle,$value);
		}
		fclose($handle);
		
		$handle=fopen($directory.$filename,"r");
		// doesn't work in the client server
		/*header("Content-type: text/x-sql");
    	header("Content-Disposition: attachment; filename=$filename");
    	header("Expires: 0");
    	header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
    	header("Pragma: public");
		
		
		while (!feof($handle)) {
					
			$contents = trim(fgets($handle));
			echo $contents;
					
		}
		fclose($handle);
		unlink($directory."/".$filename);*/
		
		fclose($handle);
		$this->setNoLayout();
		$this->assign("directory",$directory);
		$this->assign("filename",$filename);
		$this->assign("host",$host);
		$this->render();
		
		
	}
	
	public function excelImporterAction(){
		$this->setNoLayout();
		$this->render("excel.tpl");
	}
	
	public function excelImporterRefreshAction(){
		
		Zend_Loader::loadClass("MenuUtility");
		Zend_Loader::loadClass("AuthUtility");
		
		$auth = BaseAuth::getInstance();
		$this->setLayout("admin");
		$this->assign("username",$auth->username);
		
		$menuUtility = new MenuUtility();
		$this->assign("menuUtility" ,$menuUtility);
		
		$this->setLayout("admin");
		$this->render("excel.tpl");
	}
	
	/*public function importExcelAction(){
		
		Zend_loader::loadFile("ExcelUtility.php");
		Zend_loader::loadClass("ExcelFormat");
		Zend_loader::loadFile("ExcelSavers.php");
		Zend_loader::loadClass("ExcelTimeSaver");
		
		$excelFormat	=		new ExcelFormat();
		$excelFormat->addRow(new ExcelFormatRow("Date",0,0,1));
		$excelFormat->addRow(new ExcelFormatRow("Start",0,0,3));
		$excelFormat->addRow(new ExcelFormatRow("End",0,0,4));
		$excelFormat->addRow(new ExcelFormatRow("Hours",0,0,5));
		$excelFormat->addRow(new ExcelFormatRow("Description",0,0,6));
		
		$excelUtil		=		new ExcelUtility($_FILES['file']['tmp_name']);
		$excelUtil->ImportExcel($excelFormat,new ExcelTimeSaver);
		die("x");
	}*/

	public function excelImportPaymentToFishersAction(){
		Zend_loader::loadFile("ExcelUtility.php");
		Zend_loader::loadFile("ExcelSavers.php");
		Zend_loader::loadFile("ExcelFormats.php");
		
		$excelUtil		=		new ExcelUtility($_FILES['file']['tmp_name']);
		$excelUtil->ImportExcel(new PaymentToFishersFormat(),new PaymentToFishersSaver());
		
		$this->excelImporterRefreshAction();
	}
	
	public function excelImportPaymentSummaryForFishersAction(){
		die("summary");
	}
	
	public function excelImportRejectCodesAction(){
	
		Zend_loader::loadFile("ExcelUtility.php");
		Zend_loader::loadClass("ExcelFormat");
		Zend_loader::loadFile("ExcelSavers.php");
		Zend_loader::loadClass("ExcelRejectCodesSaver");
		
		$excelFormat	=		new ExcelFormat();
		$excelFormat->addRow(new ExcelFormatRow("Code",0,0,1));
		$excelFormat->addRow(new ExcelFormatRow("Value",0,0,2));
		
		$excelUtil		=		new ExcelUtility($_FILES['file']['tmp_name']);
		$excelUtil->ImportExcel($excelFormat,new RejectCodesSaver());
		
		$this->excelImporterRefreshAction();
	}
	
	public function excelImportExporterOrderListAction(){
		die("exporter");
	}
	
	/* data migration
	 * sample call : http://{domain}/admin/migrator/migrate_key/aoato/org_id/6
	 * 		 aoato : add_organisms_attributes_to_organization
	 * 		 aotti : add_organisms_to_tank_inventory
	 * 		 cuoa  : clean_up_organism_attributes
	 * 		
	 * notes 		: you must be login as admin to run this action
	 * created by 	: michael.ocana
	 * on : march 2010
	 */
	public function migratorAction(){
		Zend_Loader::loadClass("EcoezUtility");
		
		$ecoez = new EcoezUtility();

		$action = $this->getParam("migrate_key");

		if (isset($action)) {			
			print "Migration on process (if no 'done' at the bottom, please re-run again')..., please wait";
			if ($action == "aoato") {
				$organization_id = $this->getParam("org_id");
				$ecoez->add_organisms_attributes_to_organization($organization_id);
			} else if ($action == "aotti") {
				$organization_id = $this->getParam("org_id");
				$ecoez->add_organisms_to_tank_inventory($organization_id);
			} else if ($action == "cuoa") {
				$organization_id = $this->getParam("org_id");
				$ecoez->clean_up_organism_attributes($organization_id);			
			}
		} else {
			print "Check migration key from admin.";
		}
		$this->setNoLayout();
		$this->render("empty.tpl");		
	}


	public function scriptaAction(){
		Zend_Loader::loadClass("DataUtility");
		
		$db = new DataUtility();
		
		//$params = array("condition" => "sz.size_code = 'MA'","tank_query" => "tnk.name = 'FS 1.3'");
		//$params = array();
		//$inventories = $db->get_organisms_inventory(6, $params);
		//print_r($inventories);

		
		$this->setNoLayout();
		$this->render("empty.tpl");		
	}	
	
	/*
	 * add new size to attribbutes and organization
	 * parameters : organization, organism_id, size_code
	 *   admin/addSizeE/orgid/6/oid/8/scode/L
	 */   
	public function addSizeEAction() {
		$organization_id = $this->getParam("orgid");
		$organism_id = $this->getParam("oid");
		$size_code = $this->getParam("scode");
		
		print "<pre>";
		print "\r\n"."organization id : ".$organization_id;
		print "\r\n"."organism id : ".$organism_id;
		print "\r\n"."size code : ".$size_code;
		print "</pre>";
		$this->add_to_sizes($organization_id, $organism_id, $size_code)	;
		
		$this->setNoLayout();
		$this->render("empty.tpl");			
	}
	
	function add_to_sizes ($organization_id, $organism_id, $size_code) {
		Zend_Loader::loadClass("OrganismAtrribute");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("OrganizationOrganism");
		Zend_Loader::loadClass("Inventory");
		Zend_Loader::loadClass("TimeUtility");
		
		$organism_attrbibute = new OrganismAtrribute();	
		$organism_attrbibute->organism_id = $organism_id;
		$organism_attrbibute->size_code = $size_code;
		$organism_attrbibute->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
		$organism_attrbibute->date_updated  = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
		$organism_attrbibute->created_by  = "migrator";
		$organism_attrbibute->updated_by  = "migrator";
		$organism_attrbibute->save();
		$organism_attrbibute->organism_attribute_id  = $organism_attrbibute->id;
		$organism_attrbibute->save();
		
		$atrribute_id = $organism_attrbibute->id;
		
		$org_organism = new OrganizationOrganism();
		$org_organism->org_id = $organization_id;
		$org_organism->organism_id = $organism_id;
		$org_organism->ordering_status = "Active";
		$org_organism->base_price_sale = 5;
		$org_organism->organism_attribute_id = $atrribute_id;
		$org_organism->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
		$org_organism->date_updated  = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));		
		$org_organism->save();
		$org_organism->org_organism_id = $org_organism->id;
		$org_organism->save();
		
		$org_organism_id = $org_organism->org_organism_id;
		
		$inventory = new Inventory();
		$inventory->org_id = $organization_id;
		$inventory->tank_id = 1;
		$inventory->org_organism_id = $org_organism_id;
		$inventory->organism_attribute_id = $atrribute_id;
		$inventory->quantity_on_hand = 10;
		$inventory->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));
		$inventory->date_updated  = TimeUtility::convertToSQLDateFormat(date('m/d/y H:i:s'));	
		$org_organism->save();			
		
		print "\r\n"."Done...";
	}
 }		

	

?>

